I would like to be able to push a command button on a form and establish a link to a SQL Server database and link a table. I would like do that using a UDL file, stored on a shared drive and not using ODBC. Can that be done? This is a multi user database which is accessed by in-house personnel as well as by users that come in to our network via a remote desktop connection.
I am currently doing the above using ODBC. The code behind the command button is:
DoCmd.TransferDatabase acLink, "ODBC", "ODBC;DSN=bkmcth2;Description=Data Access for Hedebrg bkmcth2 database;APP=Microsoft® Access;WSID=tmulloy;DATABASE=BKMCTH2;Trusted_Connection=Yes;TABLE=dbo.GL_JCTIMSHT", acTable, "dbo.GL_JCTIMSHT", "dbo_GL_JCTIMSHT"
I followed these steps and created a UDL:
1.Using notepad, create an empty text file and then change the extension to UDL.
2. Double click on the UDL file to bring up the Data Link Properties Dialog Box.
3. Click on the Providers tab and select the driver you wish to use.
4. Click on the Connection tab and enter the connection properties, each driver will require different settings, so I will not go into much details. All drivers will require a user name and password.
5. Click on the Test Connection button to verify your connection is working correctly. Change accordingly if you get an error.
When I click Test Connection, it says everything is OK. I have now stored this in a shared folder on the server (J:\Shared\MyFIle.udl)
If I right-click the created UDL file and open with notepad this is what it contains:
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;User ID=tmulloy;Data Source=bkmcth2;Initial Catalog=BKMCTH2
Can anyone help me put all of these pieces together? I would like to change the code I have behind the command button so that instead of using ODBC , it goes out and uses the UDL file on the network drive, makes the connection to the SQL database and then links SQL Server table "dbo.GL_JCTIMSHT" to my access application.