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

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.

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

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.

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

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

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

Select allOpen 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

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

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

Select allOpen 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

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.   :-/

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

Select allOpen 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

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?

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?

<<<<<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

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.

<<<<<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

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.   :-/

<<<<<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

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.

<<<<<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

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.

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

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.

Here ya go.  I'm very curious to see what step I missed.

Ooops, didn't hit the "Attach" button.
ClearanceItems.accdb

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

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.

Yea I did, it's called "ClearanceItems.accdb"

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?

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!!!

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.

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

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.  :-)

<<<<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

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.

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