Link to home
Start Free TrialLog in
Avatar of mlcktmguy
mlcktmguyFlag for United States of America

asked on

Options for Linking SQL tables to Access 2013

I have been working with MS Access for a long time but have always used Access as both the front end and backend DB's.

I am a complete NUB to SQL.  I'm now working on a new project that will be my first to have a SQL Server backend.  I have installed SQL and created a database on my in-office computers.  After creating the DB I created a DSN for the DB.

To link the SQL tables and views to the Access front end I use the 'External Data', ODBC Database, Link then select the DSN.  Selecting the DSN shows all of the tables and views.  I select the ones I want to link and I see them in my Access front end.  This is the procedure I have used throughout testing and it works perfectly.  However, that's the only way I know.

As we move towards production the client is setting up SQL on their server.  They asked how I want it set up, specifically if ADODB is OK?  Can a DSN be created with ADODB?  I know I can connect to anything in the backend DB using the correct connection string but I'm wondering how I would link tables without a DSN.

Any help or insight would be appreciated.
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Personally, I prefer DSN-less connections.  But don't have time to answer this in detail right now.

Check out ConnectionString.com

What version of SQL Server are you using?  I strongly recommend that you ensure that everyone using the database has the "SQL Server Native Client 11.0" driver.  If you are using 10.0 or the even earlier "SQL Server" driver, it will not correctly identify some of the newer SQL Server data types, for example, datetime2 or even the date and time datatypes in SQL Server will be recognized as strings in Access if you are not using Native Client 11.0.

HTH
Dale
Avatar of mlcktmguy

ASKER

Thanks Dale, I looked at ConnectionString.Com but didn't see anything specific to linking to Access tables.

A more accurate heading for this question would have been "How to link a SQL table to Access using a 'DSNless' connection."

DNS-less connection make a lot of sense to me also.  I have been able to construct connection strings I just don't see an option for using them when trying to link SQL tables to an Access front end, as Access linked tables.  At least not thru the Access 2013 interface I used to link the SQL tables on my test machine.  That interface prompts for the DSN name.  I don't see an option to enter a connection string.

I'm pretty sure we are using SQL Server 12.0.  At least nothing earlier than 12.0.
SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America 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
BTW ... I sadly discovered that we are on SQL Server ... 2008 !  So, users do not have Native Client 11 installed.  Only those of us who have SSMS installed have that driver :-(
So if you have 2012 - you will have access to the newer data types as Dale noted ... and they will show in up Access as DataTime data type ...
DatabaseMX: Do you have the code you use to relink the tables?  I also have views that are linked.  Will this method work for those as well?

With this method how would you link a new linked table?  Maybe it will be obvious when I see the code.
ASKER CERTIFIED SOLUTION
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
"With this method how would you link a new linked table?"
Any tables that I put into my local table will get linked automatically.
Code just loops through all the tables and links each one.
I generally do it the way Joe references above, I have a local table with the names of all of the tables and views I want to link to, but you if you wanted to get ALL of the view or tables, you could query the SQL Server.

Return all of the table names
SELECT * FROM <database name>.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

SELECT * FROM <database name>.INFORMATION_SCHEMA.VIEWS
Thank you.  This works exactly as code in Dale's example (revised for my connection string).

Excellent.