I have an Access database that I upsized with the wizard wishing to continue using Access for the Front End. I want to be able to connect to the SQL database without having each computer setup for the DSN. I added this to the autoexec macro.

Function autoexec()
On Error GoTo autoexec_Err

**oConn.Open "ODBC;Driver={SQL Server};" "Server=xxxxMSQL01;" "Database=Sublist;" "Trusted_Connection=yes"**

    DoCmd.OpenForm "FSublistc", acNormal, "", "", , acNormal

    Exit Function

    MsgBox Error$
    Resume autoexec_Exit

End Function

The error I get is ODBC-Connection failed.

For my testing I did create a DSN on this computer with this user that worked fine. Then when I removed the DSN connection and ran the database I for the ODBC-Connection failed.

Any ideas?


You are using sql server without user name and password authentication. Seems to be your problem right there...

Have a look at THIS link and choose the connection string that will fit your purpose. You will notice that they all include the username and password inclusions.

I tried various strings from that link and still not having any luck. Thanks

Try using ActiveX:

dim DatabaseConnection As New ADODB.Connection
DatabaseConnection = "driver={SQL Server};server=ServerName;uid=UserID;pwd=password;database=Sublist"


Hope this help.

I figured it out. When I first used the Access Upsize Wizard it prompted me for a DSN so I chose it from the Data Sources (ODBC) from the control panel on my computer. The link to the table then was (dbo.tSublistc DSN=sublist). I created a very small database that I upsized with the wizard and the link just gave (Table1 DATABASE=testconnection)

I redid my sublist database and now it is working fine.

Thanks to all who read and replied to this post.

Jane ;-)

commented: Some rep points for your getting the solution yourself +12

It was a pleasure Jane. Some rep points for your getting the solution yourself. :)

Please mark this as solved, thanx.