Solved: Importing SQL Server Tables Using Transferdatabase

[bldial]

I am trying to create a form that will allow users to import a table from our SQL Server database into Access. As I am relatively new to VBA programming in Access I tried to start simple to see how this works. My code is as follows:

Private Sub Command0_Click()

DoCmd.TransferDatabase acImport, "ODBC database", "ODBC;DSN='Data Set Name'; uid='User1'; database='dbname'", acTable, "dbo.table1", "tablename"

End Sub

I am getting "The ODBC database type isn't an installed database type or doesn't support the operation you chose." as an error message. Are there certain references I should have checked for this to work? I know the ODBC connection is good because I can manually import the desired tables. Also, I'm using Office 2003 and Windows XP. Any help would be much appreciated. :banghead:

[stanl]

First, you can check for the SQL server driver by locating your ODBC data sources in Control Panel - should look something like attached jpg. Next, based on my [albeit limited] experience, you might be better off using ADO or OLEDB [and possibly use MSDE as middleware as it was designed to bridge Access/SQL Server.

Next, what if you just try to manually link your tables to Access?

.02
Stan

[bldial]

Thanks, Stan. The driver is there and I've tried using both ADO and OLEDB (although I may have had them checked at the same time which could cause conflicts?) I haven't considered using MSDE and will give that a try. I could do this manually but would like to create a less user-hostile utility for importing newly created tables for people with very limited software skills.

[XLGibbs]

In addition to the driver you have to make sure you set up the ODBC connection as a user or File DSN in the same window that Stan pointed out. If the machine does not have the ODBC source in it's user or file DSN set up, you won't be able to connect.

I have code that installs an ODBC source on the user's computer, but it is sketchy and I have not bullet proofed it.

[stanl]

or, if there are multiple users go with a UDL - easier to manage IMO. Stan

[XLGibbs]

Yes, if this is a distributed application a UDL is the way to go......

You can create a UDL file by right clicking a folder window and selecting new text document. Rename the blank text document with a .udl extension. Double click the file and it will be self explanatory from there to set it up. Then you can point the connection string to the UDL file for the connection properties without setting up a ODBC connection.

Example only:


Private Sub Command0_Click()

dim objCN as New Connection
ObjCN.ConnectionString = "File Name= C:\FolderName\UDLFILE.udl"

DoCmd.TransferDatabase acImport, strConn, acTable, "dbo.table1", "tablename"

End Sub

Discussed here with other links and a sample file..
http://vbaexpress.com/forum/showthread.php?t=7048

[bldial]

Thanks. I now get a type mismatch error but maybe I can solve that one. The UDL connection looks like it will be an easier tool for distribution.

[bldial]

Well, I discovered the problem I was having was in labeling the database type as "ODBC Database" (as listed in the VBA help) vs. "ODBC" (the correct method). All that frustration for 1 word. Thanks for the help!