Connecting to SQL Server with Code in AccessXP

[Anthony C]

Hi All!

I have an Access XP app that has an "automatic" connection string (run via a
function called in the autoexec macro) to connect my application to 2 SQL
server back-end databases. The string is identical (other than the variable
db1 for first SQL server and db2 for second), and connects fine to the first
database, but will not connect to the second database. So, my users are
continuously promped via the SQL Server Login box to log into the second
database. I know the pitfalls of doing this in code, but the database has
its own security, as well as the fact that the front-end is converted to
..mde before users get a hold of it.

Here is my function, which is called from autoexec:

Function EstConn()

Dim db1 As DAO.Database
Dim db2 As DAO.Database
Dim dbcurr As DAO.Database

Set dbcurr = CurrentDb()
Set db = OpenDatabase("", False, False, _
"ODBC;DSN=DSNname1;" & _
"UID=user1;PWD=password1;DATABASE=" & _
"database1;")
Set db2 = OpenDatabase("", False, False, _
"ODBC;DSN=DSNname2;" & _
"UID=user2;PWD=password2;DATABASE=" & _
"database2;")
'More startup stuff. . . .
End function

Thank you!

 

[Gabe]

Hi Anthony,
I've managed to connect Access to SQL server through an
ODBC. I can see the fields and basic table structure but
the data is blank. You seem to have experience with this,
any thoughts? Much appreciatted, Gabe

 

[Anthony C]

Gabe,

I'm definitely no expert, but I can try to help you troubleshoot. Do you
have to log in to the SQL Server? If not, it's probably automatically
logging you in as "Admin" or "anonymous", but the SQL Security is probably
not set up for anonymous users or for your account (however you are logging
in). Just a guess, but it sounds to me like you don't have read access to
the tables, just the database structure within the SQL database. I'm
definitely no expert, but if you're the SQL Admin (or you know him/her),
create a login for yourself with (at least) read access to all tables.

HTH,
-Anthony