ODBC - Connection failed to Sublist

[jane.frasier]

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
    DoCmd.Maximize

autoexec_Exit:
    Exit Function

autoexec_Err:
    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?

Thanks.

[AndreRet]

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

[jane.frasier]

So what would the syntax be to add that?

[AndreRet]

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.

[jane.frasier]

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

[elmer0727]

Try using ActiveX:

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

DatabaseConnection.open

Hope this help.

[jane.frasier]

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 ;-)

[AndreRet]

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

Please mark this as solved, thanx.