Setting up ODBC Connection

[Plague1211]

I have a little app here that is linked using and ODBC connection...The driver is installed on the computer in which it will be used, but how do I go about setting something up so that it will always connect to the specified ODBC connection, and not having to go into Control Panel ---> Administrator etc. to link the database after say, rebooting...

Ty

 

[Plague1211]

bump

 

[scriverb]

An ACCESS database may have a permanent link to an external data source through an ODBC connection. If you driver is installed follow these steps:

1. In database design mode select File menu / Get External Data / Link tables.
2. In the Files of Type combobox at the bottom of the window select ODBC databases.
3. It is this next window that you can select your Data Source(driver).
4. Your file data source for your ODBC driver should be visible in the window select it and click OK.
5. Follow the steps to link to your table.

These links can be created dynamically through code upon demand. Meaning that the connection doesn't always have to be there but only is made when you need to access the database source.

Post back with any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[mkov]

Also make sure that you check to box to save the password when you link the tables. If not, everytime you try to access one of the ODBC tables, you will be prompted for the password.

 

[cmmrfrds]

If you want to do it in code and not set up odbc on each client then here is some sample code. Assuming, in this case, a sql server login was set up.


Public Function SQLServerLinkedTable()
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={yourserver};" & _
"Database=yourdatabase;" & _
"Trusted_Connection=No;" & _
"Uid=youruserid;" & _
"Pwd=yourpassword;"

' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = CurrentProject.Connection

' Create a new Table object
Set oTable = New ADOX.Table

With oTable
.Name = "dbo_yourtablename"
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = "dbo.tblReportRequestPrelim"
.Properties("Jet OLEDB:Cache Link Name/Password") = True
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With

' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh
Set oCat = Nothing

End Function

 

[Plague1211]

Scriver, with your method, would I have to do that on each PC that the application would be running?

CMM: For that to work, would I just have to change the first set up code

Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={yourserver};" & _
"Database=yourdatabase;" & _
"Trusted_Connection=No;" & _
"Uid=youruserid;" & _
"Pwd=yourpassword;"

to what the ODBC information is, or do I also have to change the 2nd set

With oTable
.Name = "dbo_yourtablename"
Set .ParentCatalog = oCat
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = "dbo.tblReportRequestPrelim"
.Properties("Jet OLEDB:Cache Link Name/Password") = True
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With

 

[scriverb]

No you would only have to do that on your development PC as long as the other PC's have the same driver installed with the same name. The application can be distributed with this link already installed and the connection would be made when the database is opened and the file accessed.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[cmmrfrds]

The first part is setting up the odbc connection and the second part is linking the table.

The following should be your table name.
.Properties("Jet OLEDB:Remote Table Name") = "dbo.yourtablename"

 

[Plague1211]

Ok, so If I'm following correctly, all I have to do is link the tables initially, and if the ODBC driver is installed on the computer, then it will work?

I already have the tables linked on the development copy of the DB, and when inside the firewall, I can access the tables....Now, if I were to send the DB to say PC B, and it has the ODBC drivers installed, and all I do is run the DB, and say, run a query on Table X, and table X is odbc'ed, will it work, or will I hve to do anything special to it?

 

[scriverb]

Yes that should work, but make sure you follow mkov's advise and check the save loggin and password check box so that the user isn't prompted to have access to the connection.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]