Link to home
Start Free TrialLog in
Avatar of Merlin-Eng
Merlin-EngFlag for United Kingdom of Great Britain and Northern Ireland

asked on

DSN-LESS connection to MS Access database

MS Access 2010:  Sorry if this is complicated.... My database is linked to a SQL Server, but for the time being, it is also linked to some data which is in an MSAccess .ACCDB file on the network. I do this with standard linked tabledefs which MS Access provides.  However, I'm having performance issues when running reports using the linked tabledefs, but I know that if I open an ADODB recordset to the MS Access data, I can get at the data much quicker.

I already use DSN-less queries for my reports to access the SQL Server data . So I was wondering if I could set up a DSN-Less query to the MS Access back end. I can't seem to get the Connect String in the correct format though.

If I try this:

ConnectString = "ODBC; Provider=Microsoft.ace.OLEDB.12.0; Data Source=\\Server\Share\BackEnd.accdb;"

This connect string is accepted when I apply it to the .Connect property of the QueryDef. But when I try to open the query, Access pops up with the dialog asking me to select the ODBC Data Source.

I thought maybe I should try it without the 'ODBC' statement. But when I do this, Access won't let me apply the Connect string saying that the connect string is in an incorrect format.

Can anybody help me here please? Or maybe Access just won't allow me to use DSN-Less queries to attach to MS Access databases.

Ian
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Do you mean you are creating a PassThrough Query to an Access database?

Or do you mean you are creating a DSNLess linked table to an Access database.
Avatar of Merlin-Eng

ASKER

Scott, thanks for your reply. I've always known them as DSN-Less queries. It's a QueryDef with a connect string configured to point to the data source.  Once correctly configured, the querydef shows up with a Globe symbol at the side of the name. When i think about it, I've seen other people referring to them as DSN-Less queries also.
ASKER CERTIFIED SOLUTION
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Kelvin , that makes perfect sense to me..... A DSN-less connection requires a client-server environment. I have this with my SQL Server data, but for the MS Access data, my application is going to have to have direct access to the data file.  I use ADODB most of the time for opening the back-end .ACCDB file and crunching data. It just a shame that my forms and reports have to use tabledefs and querydefs in order to retrieve recordsets. I know I can cache data in local tables to speed things up, but this isn't always practical. Thanks for your input.
Remember, you can now bind forms to recordsets - maybe an ADODB recordset may help you.


Kelvin
I didn't know you could do this.  With sql server data I modify the .SQL property of the dsn-less query and use the query as the RowSource property of the form.

So now I know I can open an ADODB recordset and bind this to the forms Recordset property. I assume that this can still happen if the Recordset has been opened from a remote database.  This gets around using the linked Tabledefs which slow things down.

I think this solves my problem completely. Thank you so much for your help.

Ian