ADO connection with Access 2013 to Sql 2008 (1 Viewer)

Sweetu · Nov 29, 2016

Hi dears Experts!

I need your help please...
I've access 2013..which is connected to Sql 2008 via ODBC...
Before I was using query for forms to filter required data...
But after migrating tables from access to sql query takes much longer time to display in forms....
And every time I need to put credentials for sql to connect...
I want to use ADO connection with sql and call the query from sql to lower the latency rate....

Or if any other solution to use or filter records in forms with faster speed...
Server is far away from front end access...
I'm unfamiliar with ADO even I get the syntax for ADO connection with Vba but I don't know where to use and how to call the query or use recordset....

Please guide me....

Thanks

Ranman256 · Nov 29, 2016

connect the tables via ODBC, then the credentials are already loaded.

Sweetu · Nov 29, 2016

Ranman256 said:
connect the tables via ODBC, then the credentials are already loaded.

I connected already via ODBC...but every time it requires sql login and password...

Can you help me with ADO please?

Minty · Nov 29, 2016

Make sure your queries are operating on Indexed fields, and if necessary add more indexes to commonly queried fields.

Sweetu · Nov 29, 2016

Minty said:
Make sure your queries are operating on Indexed fields, and if necessary add more indexes to commonly queried fields.

I checked everything is fine and indexed properly,
just sql credentials is making me crazy...and searching via filter on to existed query takes 2 minutes...
Before migrating to sql it filter out the records within seconds...:banghead:

Someone suggested me to create ADO connection and add the path to sql and credentials...

But I don't know how to do...

Minty · Nov 29, 2016

Is this just you using it or many users ?
I save our all our ODBC as connections as System DSN datasources, set it up once and forget it. Not so easy if you are having dozens of off site connections.

Sweetu · Nov 29, 2016

Minty said:
Is this just you using it or many users ?
I save our all our ODBC as connections as System DSN datasources, set it up once and forget it. Not so easy if you are having dozens of off site connections.

Yes dear now you got it right...I need to set up DSN datasource connections....
I've to distribute this to 8 devices...
Can you please teach me...how to do?
Thanks for your reply though...

Rx_ · Dec 28, 2016

A few years ago, I posted DSN-Less or DSNless connections.
Several great programmers from this site chipped in.
It is basically a vba subroutine that deletes all links then uses a script to create the linked tables with out an DSN (on the desktop).
It is basically the same process used for Azure SQL Cloud services from Access.

My favorite is to download SQL Server Native Client on a workstation then use the code. It is the preferred method for MS Access to Azure
Example of several discussions:
http://www.access-programmers.co.uk/forums/showthread.php?t=224121&highlight=Native

Here is a link, where I cited other links on SQL Server Native Client.
http://www.access-programmers.co.uk/forums/showthread.php?t=247756&highlight=strConnectionString%2C+native

Basically, download the ODBC upgrade on to a PC once. Then use it with VBA to connect to SQL Server, IBM, and other data sources.
It prevents the need to go to each PC and maintain the DSN dialogue box.

ADO connection with Access 2013 to Sql 2008 (1 Viewer)

Sweetu

Registered User.

Ranman256

Well-known member

Sweetu

Registered User.

Minty

AWF VIP

Sweetu

Registered User.

Minty

AWF VIP

Sweetu

Registered User.

Rx_

Nothing In Moderation

Similar threads

Users who are viewing this thread