Access front-end with SQL Server OLEDB

[adibakale]

I am creating an Access application that we need to use with SQL Server. I have a some questions that I have not been able to get answers for yet after doing quite a bit of research.

I am planning on using a OLEDB connection, However I am a little confused about how to set this up. I have an existing Access ADP database that connects to SQL Server automatically when it is opened using a connection string, I am able to see that by doing debug.print currentproject.connection.

I am creating this new application using accdb since ADP is no longer supported. The question I have is, Where do I store or set up the OLEDB connection string?

 

[Micron]

I see no responses but some views. My experience is limited on your issue, but what the heck.
Wondering why if you can view the connection string in the vb editor you can't replicate the code that handles this and put it in the same sort of routine.
I'm thinking an AutoExec macro would call code from a standard module to make the connection between your front end and back end. My experience with connections to non Access tables has been to use the Access 'get external data' tool from the ribbon and make the connection that way, using ODBC. Sorry if that doesn't help.

 

[xenou]

Also more advice on using ODBC (which is all I have experience with as well):
https://www.mssqltips.com/sqlserver...ess-linked-tables-with-a-sql-server-database/
MS Access for the Business Environment: Extend Access with Pass-Through Queries — DatabaseJournal.com

I believe the difference between the two links above is whether you explicitly create a DSN or just store the ODBC connection information in the query properties. I've used the latter approach (once I have a query that works, I just copy it and adjust the sql to create new queries without having to repeat the steps).