Re: Access 2007 ADP (Runtime) and SQL Express 2005

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Okay, point taken. As a learner i'm bound to make mistakes.

most of what I have learn't comes from information collected over the
internet.

I have removed trusted connection from the connection syntax.

Do you know of any resources on the internet where I can progress further
with ADP and SQL express 2005. I especially would like guidence on getting
SQL express on a network with the ADP clients connecting to it (LAN setup) .

My attempts at trying with two pc connected via a network hub seems to be
failing. I have a default installation of SQL express on one Pc, mixed mode.
The second PC cannot see the sql instance on first Pc.

Any pointer would be appreciated.

"Norman Yuan" wrote:


"Hans" <Hans@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BF35DB0C-2864-455A-81F5-C9482B5CD7F0@xxxxxxxxxxxxxxxx
Thanks for that bit of information, as you know i'm on a steep learning
curve
to educated my self on this.

Okay, managed to setup the alias, I was wondering if you knew the
connection
syntax for connecting to SQL Exresss from within ADP using alias, I
currently
use:-

dbConn = "Provider=SQLOLEDB.1" _
+ ";Server=OVSL-2\QiSSystem" _
+ ";Database=2iQ-QiS" _
+ ";User ID=" & Forms![Global]!username _
+ ";Password=" & Forms![Global]!Password _
+ ";Trusted_Connection=True" _
+ ";Integrated Security=SSPI;"

The above works fine, please advise if you can improve on the above.


No, the above DOES not work fine (even it may work on your computer fo r
some reason). The ConnectionString's security part simply does not make
sense: if you use Trusted_Connection (Integrated Security, they are the same
thing here), you never need to pass User ID/Password. Also, since you use
SQL Server Express, you can only use UserID/Password when SQL Server's mixed
security mode is enabled (it is not by default).

This indicates that more study on SQL Server security is needed before you
can deal with user logging into the database correctly.


.

Relevant Pages

  • Re: Pessimistic locking with approles.
    ... The "fix" is to disable connection pooling in ADO in the connection ... security or SQLS security, and it has nothing whatsoever to do with ... SQL Application Role Errors with OLE DB Resource Pooling ...
    (microsoft.public.sqlserver.security)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... SQL CE/Mobile has a background thread that flushes the changes ... they run for long hours and we have not seen memory leaks so far. ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... So it is a kind of asynchronous commit which happens every 10 ... I am not sure of this claim as we have many applications built on SQL CE ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
  • Re: WinXP Firewall and Sql Server
    ... Larry - the protocols/ports used by the enumeration are exactly the ones you ... DON'T want to open when connected to the internet. ... Why not just type in the name of the SQL Server you want to connect to? ... > VStudio/VS.NET connection dialog. ...
    (microsoft.public.sqlserver.security)
  • Re: WinXP Firewall and Sql Server
    ... Larry - the protocols/ports used by the enumeration are exactly the ones you ... DON'T want to open when connected to the internet. ... Why not just type in the name of the SQL Server you want to connect to? ... > VStudio/VS.NET connection dialog. ...
    (microsoft.public.sqlserver.security)