?
Solved

How to create an Access database with a DSN-less linked table to a SQL database?

Posted on 2012-08-20
38
Medium Priority
?
6,389 Views
Last Modified: 2012-09-12
Hey guys, my question is pretty straight forward.  There is a database on our SQL 2008 server and it has just one table in it.  All I want to do is link to that table from an Access 2007 database where a person can make changes and additions.  I know how to create a DSN and then link a table via a DSN using ODBC, that's easy.  But here's kicker:   I want to put this Access database file on a shared drive and let a good amount of people open it up and read/write to it.  When I do that using the ODBC & DSN method, as soon as they try to double click the table it fails throwing an "ODBC--connection failed" error because the DSN I created on my computer when I first created the Access file doesn't exist on all the users computers.  

So..........how do I have Access work like I want to but without the need of a DSN?  Can I use an ADO connection string in stead?  That would be awesome if I could.  Thanks guys!
0
Comment
Question by:tphelps19
  • 19
  • 18
38 Comments
 
LVL 2

Author Comment

by:tphelps19
ID: 38314128
I also should say, I'd be fine if someone could update this database from Excel also.  I just need a way people can update the data in a SQL database table without logging into SQL themselves or without me writing a custom webpage to modify/update the data.  Access just seemed like the easiest way.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38314541
Try this ... I do this all the time using MySQL which will work the same for SQL Server ... Just using a different ODBC driver.

1.) Create a little Access Front-End application and distribute that to each machine.  You should not have multiple users opening the same Access db at the same time.

2.) In the Access front-end, create a Pass-Thru Select Query that will basically select the records from your 1 table on SQL Server.

3.) I normally use a 1 record setup_general table in the front-end application to store the SQL/MySQL login parameters.  Then use a function on startup of the app or using the startup form to populate and refresh a global variable used in the connection as shown below.  Distribute your front-end as a .mde file for security reasons, if need be.

'Use this function to build your connection string:
Function CreateMySQLConn()
    Dim strMySQLServerHost As String
    Dim strMySQLDatabase As String
    Dim strMySQLLogin As String
    Dim strMySQLPswd As String
   
    strMySQLServerHost = DLookup("[MySQL_SERVERHOST]", "Setup_General")
    strMySQLDatabase = DLookup("[MySQL_DATABASE]", "Setup_General)
    strMySQLLogin = DLookup("[MySQL_LOGIN]", "Setup_General")
    strMySQLPswd = DLookup("[MySQL_PSWD]", "Setup_General")
   
    strMySQLConn = "Driver={MySQL ODBC 5.1 Driver};Server=" & strMySQLServerHost & ";Database=" & strMySQLDatabase & ";User=" & strMySQLLogin & ";Password=" & strMySQLPswd & ";Option=35;"

'NOTE: For SQL Server, change Driver= to   {SQL Server}
End Function


'Use this in the form's Open Event
Private Sub Form_Open(Cancel As Integer)
Call CreateMySQLConn   <--------sets up your connection string for the query to your table.

Dim db As DAO.Database
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryYourSQLTable")

qdf.Connect = "ODBC;" & strMySQLConn

qdf.Close
set qdf = Nothing
db.Close
set db = Nothing
End Sub

You can then use the query in the front-end Access app as the Record Source of a data entry from into your SQL server table.

Also, you will probably leave off the Option=35 in the Connection string as that relates to MySQL.

HTH ...

ET
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38314689
I hear what you're saying about a query based Access file but you've lost me on how to implement what you posted.  How do I use a "function" on start up?  Where do I put that?  And for the login credentials, for this particular DB I don't mind hard coding the username & password.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38314759
Ok if that's the case ...

1.) Create your Passs-Thru Query in the Access Front-End (SQL Only Query).
SELECT * FROM dbo.YourTableName ORDER BY YourFieldName;

2.) Go to the Properties for this query and enter the SQL Server connection string on the ODBC Conn Str line as shown below:

ODBC;Driver={SQL Server};Server=xx.xxx.xxx.xx;Database=database_name;Uid=username;Pwd=password;

Then use the Pass-Thru query as the Record Source of your data-entry form.


ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38314770
Also, I assume you will be using a Access Form Object to allow users the ability to either add, delete and or modify records.

ET
0
 
LVL 85
ID: 38315901
You could simply link the table, instead of creating a Pass-Through. Depending on what you're doing with the data, that can be more than sufficient for your needs.

To do that, just run code on startup to create the link.
Dim sConnect As String
sConnect = "ODBC;DRIVER={sql server};DATABASE=YourDatabaseName;SERVER=YourServerName;Trusted_Connection=Yes;"

Dim tdf As DAO.TableDef
Dim dbs As DAO.Database

Set dbs = CurrentDB
Set tdf = dbs.TableDefs("YourTableName")

tdf.Connect = sConnect
tdf.RefreshLink

Set tdf = Nothing
Set dbs = Nothing

Open in new window

Obviously you'd need to change the Server and Database names in the connect string to reflect your own values. If you need to pass in a username/pass, then you'd need to change the connect string (see the link below).

For 2008, you'd probably use the SQL Native Client driver, for example.

If you need different connect strings:

http://www.connectionstrings.com/

More info on DSNless connections:

http://www.accessmvp.com/djsteele/DSNLessLinks.html
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38316121
Yes, as LSM posted you could use a linked table as well.  I only provided the Pass-Thru Query option because tphelps19 stated ....

<<<<< How do I use a "function" on start up?  Where do I put that?  And for the login credentials, for this particular DB I don't mind hard coding the username & password.>>>>>

The Pass-Thru Query option will allow you to easily hard code the SQL Server Connection String directly in the query properties.

ET
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38316246
Also, just looking at it ... you will probably have to modify LSM's code a bit as shown below to create the table def.  Otherwise, you will probably get a "Item not in collection" error.  
You will also need to delete the table first if it exist in the table defs collection.

Function LinkTbl()
Dim sConnect As String
sConnect = "ODBC;DRIVER={SQL Server};Server=xx.xxx.xxx.xx;Database=aqua1;Uid=xx;Pwd=xxxxxxxx;"

Dim tdf As TableDef
Dim dbs As Database

If DCount("Name", "MSysObjects", "Name = 'dbo_RM00101' and Type = 4") <> 0 Then 'The table exist
    DoCmd.DeleteObject acTable, "dbo_RM00101"
End If

Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("dbo_RM00101")
tdf.SourceTableName = "dbo.RM00101"
tdf.Connect = sConnect
dbs.TableDefs.Append tdf
dbs.TableDefs.Refresh


Set tdf = Nothing
Set dbs = Nothing

End Function

Open in new window




Also, note ... SQL Server will probably have a table name like dbo.XXXXXX.  In Access, the Period (.) in the file name  will cause an error when creating the table def.  I normally change the name to dbo_XXXXXX as shown in the example above.

Just change dbo.RM00101 and dbo_RM00101 to your actual file names.


ET
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38316454
Guys, I'm very familiar with vbscript and vba but I've just never done access before.  Is it still ALT+F11 to get into the VBA editor?  You've said a few times to put that code in my startup object........what the heck is the start up object and how do I put it in there?  I'm very familiar with databases but just not with Access.   :-/
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38316679
Assuming you are going to use the Linked Table Method ?????

1.) Create a new Module in Access then paste the function below in it.

Function LinkTbl()
Dim sConnect As String
sConnect = "ODBC;DRIVER={SQL Server};Server=xx.xxx.xxx.xx;Database=aqua1;Uid=xx;Pwd=xxxxxxxx;"

Dim tdf As TableDef
Dim dbs As Database

If DCount("Name", "MSysObjects", "Name = 'dbo_RM00101' and Type = 4") <> 0 Then 'The table exist
    DoCmd.DeleteObject acTable, "dbo_RM00101"
End If

Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("dbo_RM00101")
tdf.SourceTableName = "dbo.RM00101"
tdf.Connect = sConnect
dbs.TableDefs.Append tdf
dbs.TableDefs.Refresh


Set tdf = Nothing
Set dbs = Nothing

End Function

Open in new window


2.) How are you planning on allowing the users to interface with the access front-end application and table???  Assuming you will be using some type of Form Object as an interface for your users???
Create a little main form or switchboard form that opens at startup of your application.  You will set this as the default in the startup properties of your Access db.  This should introduce your application with a Command Button on it to Open your Detail Form that's bound the the SQL Server Linked table.  Set the On Open Event of your startup Form to:

Private Sub Form_Open(Cancel As Integer)
     Call LinkTbl
End Sub


Or, you could create a Macro in Access, name it AutoExec with one Action, RunCode .... LinkTbl().  This will run the function at startup of your database.

ET
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38316704
Ok I think this might be way too complicated, let me start from scratch:

All I want is for me to be able to send a user a link to this central Access file and when they double click on it, it will open up to a regular table view of the one table in the SQL server and they can add, delete or modify any records in that table.  Simple as that.  I fine with hard coding the SQL username/password, but that is all I want to do.  I want it to be EXACTLY like the linked table method you can do in Access with the exception that I don't want each user to have to have a DSN on their computer.  Does that make a little better sense?
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38316714
The linked table method in Access works beautifully, the only problem is it requires a DSN on everyone's computer.  That is the only part I'm trying to eliminate.  Do you know to do that?
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38316859
<<<<<All I want is for me to be able to send a user a link to this central Access file and when they double click on it, it will open up to a regular table view of the one table in the SQL server and they can add, delete or modify any records in that table. >>>>>

Will multiple users be hitting the Access db at the same time????  If so, you will want to distribute a separate Access db to each user.  Multiple users, opening the same Access db at the same time will cause you some problems.

Include this code in your AutoExec Macro to open the table.

DoCmd.OpenTable "YourTableName", acViewNormal  


<<<<<The linked table method in Access works beautifully, the only problem is it requires a DSN on everyone's computer.  That is the only part I'm trying to eliminate.  Do you know to do that?>>>>>

You will not need a DSN on everyone's computer.  The connection string replaces the DSN.   As long as the SQL Server driver is present you should be ok.  I think it is installed by default with Windows or Office.

ET
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38316882
I think we're making progress, but I don't know what an autoexec macro is or how to create one.  :-P

And yes, I was planning on having everyone use the same Access file but I can just as easily distribute one to each of them if you're saying that will work better.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38316959
<<<<<And yes, I was planning on having everyone use the same Access file but I can just as easily distribute one to each of them if you're saying that will work better.>>>>

Yes, that's the best method ... having multiple users opening the same Access db at the same time will cause all sorts of conflict errors, etc.

<<<<<I think we're making progress, but I don't know what an autoexec macro is or how to create one.>>>

I'm using 2010 but I think the Access Ribbons in 2007 are the same.  
Click the Create Tab in the Ribbon at the top.  You should see a section name Macros & Code.  Click Macro then Add New Action.  The Action you want is RunCode.  The Function Name will be LinkTbl().  This is the function you created in the new module.  Save and name this Macro AutoExec.  It will run each time your database opens.

ET
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38316979
Actually Access 2007 is quite a bit different than 2010 but I found what see what you are talking about.  When you say:

<<<<< "This is the function you created in the new module" >>>>>

I have no idea what you mean here.  What new module???  Again, Me = Access Newb.   :-/
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38317051
<<<<<I have no idea what you mean here.  What new module???  Again, Me = Access Newb.>>>>>

See screen shots posted below.

You have to create a Visual Basic Module .... Which is a Access Object just like Tables, Forms, Reports, Queries, etc.   Make sure you include the Option Explicit line, ok.
vbModule.JPG
vbModule2.JPG
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38317054
And whoever said that you don't need a DSN on each user's computer has no idea what they are talking about.  If I create an Access 2007 file and then also create a DSN that connects to that computer, then link a table using that DSN and save the file and then try to open it from another computer it WILL FAIL!   That is the WHOLE problem I'm trying to solve here and I think everyone is totally misinterpreting what I'm asking for.  I don't care that if the actual table auto opens, the users can double click the table themselves, I just need to make sure it works without each person needing a DSN.  I seriously wouldn't think it would require VBA code in order to do something so common as this.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38317088
<<<<<And whoever said that you don't need a DSN on each user's computer has no idea what they are talking about.  If I create an Access 2007 file and then also create a DSN that connects to that computer, then link a table using that DSN and save the file and then try to open it from another computer it WILL FAIL!   That is the WHOLE problem I'm trying to solve here and I think everyone is totally misinterpreting what I'm asking for.  I don't care that if the actual table auto opens, the users can double click the table themselves, I just need to make sure it works without each person needing a DSN.  I seriously wouldn't think it would require VBA code in order to do something so common as this.>>>>>

Because you originally linked the table using a DSN and that information is stored with the table.  When the other user tried to open it, there is no DSN on their machine, so it failed like it should.

I think you are confused in that you are trying to learn Access on the fly and not really following the steps correctly.

1.) The code in the function WILL establish the linked table WITHOUT a DSN on the machine.  That's to purpose of using a Connection String vs. a DSN.

2.)  I also gave you the option to embed the Connection String directly in a SQL PASS-Thru query to your SQL Server also not requiring a DSN.

Both methods work without a DSN.

ET
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38317092
Ok, getting a little better......

When I created the module and then went to close out it asked me if I wanted to save "Module1"?  I have done Excel VBA and it's never cared what the module name was, any reason why Access is different?  I just called the module "linkTbl" because I didn't know what else to call it.  Am I supposed to call that module "AutoExec"?

The AutoExec Macro I created is failing saying "The express you entered has a function name that Microsoft Access can't find."   So I renamed the module to "AutoExec" and I think that fixed it, however now I'm getting an error:  "Run-time error '3011':   The Microsoft Access database engine could not find the object 'dbo.RM00101'.    ???

btw, sorry if I'm a little frustrated, I just can't believe I have to write VBA code to do something gosh darn common as this task and MS would not have a totally friggin easy way to do this by now.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38317108
Not a problem!!!

<<<<<<When I created the module and then went to close out it asked me if I wanted to save "Module1"?  I have done Excel VBA and it's never cared what the module name was, any reason why Access is different?  I just called the module "linkTbl" because I didn't know what else to call it.  Am I supposed to call that module "AutoExec"?>>>>

You can name the module anything you want or accept the default "Module1".  No, the module should not be name AutoExec.  The Macro itself should be named AutoExec.

What is the name of your table in SQL Server.

Please upload your db so this can be resolved without spending more time on it.  Sometimes a picture is worth a thousand words and Access can be challenging for a new user.

ET
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38317117
Ok let me start over......

I have a blank Access 2007 database that I just created.  What steps should I do in order to get this done?  Because I'm obviously too stupid to understand your steps that you obviously think are so simple to follow.  From a BLANK ACCESS 2007 DATABASE.....  do I need to do the linked table first?  Or do I just do the AutoExec Macro and module?  What do I name the module (nobody mentioned that step at all).

Guys I appreciate your help but this right here is why so many people HATE IT guys, because they can't seem to explain things in a way that people with less experience understand.  What I'm looking for is someone to say, "Start from a blank database, click on Create tab and then Macro and then Add.  etc......etc......etc....."    Those instructions are SUPER easy to follow whereas someone copying/pasting a VBA function and saying there ya go.  Just hang with me for a little bit more guys, I think we're right there....I just can't seem to connect the dots.
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38317130
Here ya go.  I'm very curious to see what step I missed.
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38317136
Ooops, didn't hit the "Attach" button.
ClearanceItems.accdb
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38317156
Just hang in there typhelps19 ... It's a new environment for you so things will be a little different.  I understand though ... It can be confusing if you are not familiar with the environment.

Did you upload your db??

ET
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38317209
And here is the all the info about the server & database:

SQL Server:        ALT-DB01
SQL Database:   ClearanceDB
SQL Table:          tblItems
Username:         clearance_write
Password:          clearance

This has no confidential data whatsoever and is only accessible from internal network IP's so no worries about security.
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38317228
Yea I did, it's called "ClearanceItems.accdb"
0
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 2000 total points
ID: 38317234
Ok, pretty easy fix.  You followed everything for the most part except in the function, you did not put in your actual table name.  In the example, it's using a table name from the sample code.  I assumed you were going to adjust that for the actual names, etc.  See below, I black out your server and login parameters for security reasons.

vbModule3 Sample
Also, I attached your fixed sample db.  Run that one and see what it does.

ET
ClearanceItemsFixed.accdb
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38317258
The AutoExec macro still isnt' working.  It says, "The expression you entered has a function name that Microsoft Access can't find".   I double checked the spelling and it's all correct so I'm not sure why it's not working?
0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 2000 total points
ID: 38317293
I still think that for what you are wanting to do without the use of VBA, the easiest method would be my original solution.  In Access, create a SQL Pass-Thru query to your SQL Server table and hard code the connection string directly on the Conn Str line of the query Properties.

Query SQL:

SELECT * FROM dbo.tblItems;



Query Connection String:

ODBC;Driver={SQL Server};Server= ALT-DB01;Database=ClearanceDB;Uid=clearance_write;Pwd=clearance;


ET
0
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 2000 total points
ID: 38317332
<<<<<The AutoExec macro still isnt' working.  It says, "The expression you entered has a function name that Microsoft Access can't find".   I double checked the spelling and it's all correct so I'm not sure why it's not working?>>>>

Open your Module LinkTbl and rename it Module1 so it does not share the same name as the function.

Then add Public to the front of the function as shown below ... You need Public declared so the Macro can see it.... my mistake there.

Public Function LinkTbl()

ET
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38317374
FINALLY!!!
Ok, that finally did it.  That was why the Macro "AutoExec" couldn't find the function in the module.  I also found I had to give Macro Security the lowest level and check "Allow" under the Trust Center.  I also didn't realize that a "Pass Through Query" would allow you to modify the data, that was confusing me a bit.  I'm so much of a SQL guy that to me a SELECT statement means just that, not UPDATE....ya know?

Thanks for all your help!!!
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38317388
On a follow up note, turns out I was correct on the "Pass-Through Query".  It does NOT let you update or add new records.  It only lets you READ them.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38317547
Ok, great ... The linked table method should work for you.

You will notice in the function where I first Delete the linked table if it already exist.  The reason is essentially what you ran into earlier.  If the table had already been linked say using a DSN then that information is saved with the linked table.  Using it on another machine without the DSN will fail. The code delete it first, then re-links it using the Connection String.

Works like a charm, I do it all the time.

ET
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38318249
Oh yea, it worked great....thank you!  I did notice that if I deleted the table and then ran AutoExec without quitting Access first, I had to run it twice but that will never be an issue.  :-)
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38318486
<<<<I did notice that if I deleted the table and then ran AutoExec without quitting Access first, I had to run it twice but that will never be an issue.  :-) >>>>>

Not sure I follow you there because the AutoExec Macro will just link the table same as doing it manually with a DSN.  Although in code, some of the GUI forms my not have been refreshed, etc.

It's probably a refresh issue.  Try this ... Manually delete, the table, run the AutoExec once, close your db then re-open it.  You should see the table there.


ET
0
 
LVL 2

Author Comment

by:tphelps19
ID: 38318520
Oh yea the autoexec code works beautifully every time you open it.  Even if I delete the table and close/reopen the database it works great.  It's just for some reason (in Access 2010), if you delete the table and then right click AutoExec and click "Run" it doesn't bring the table back the first time, you have to run it a second time for it to.  But that is only if I delete the table and run AutoExec without closing the application first.  If I close the application first and reopen it works great.  Weird huh?   But thatt's no big deal.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 38318753
Ok, glad you got it working.  Access is a pretty rich development environment especially for front-end GUI's connected to backend db's like SQL Server, MySQL and Oracle, etc.  A lot of times Access is misrepresented as a tool because most of the time it is compared head on as a database with the true relational db's like SQL Server.  Being a consultant, I generally advise most users that Access is a complete Development Environment that just happens to come with a "Free" database.   You have to do a ton of development work using other tools in order to duplicate what Access delivers.

Example:  I'm currently building a Access 2010 front-end application that will run on a Windows 7 tablet connected to the Internet using 3G/4G cellular connectivity communicating with MySQL server tables.  No linked tables at all.  All functions will use VBA and ADODB Commands & Recordsets on demand when a function is run by the field employee.  This will be used by service techs to enter time clock entries, service transactions, work orders, etc.  It is fast and the transactions are posted directly to the db tables.  While this can be done with a web app & interface, having to move the data in and out of variouse web languages can and will add tons of additional development time.  It will also be distributed with the Access Runtime so no need to install Office 2010 on each machine.  Work Great!!!  Again, Access is a very powerful tool!!!

ET
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question