Link Tables from MDB to SQL Server 1

[Hfnet]

I have linked tables in my MDB to our SQL Server, but when I tried the mdb on another PC, the connection to SQL Server failed. I realised that my pc has an ODBC connection, where the other PC's don't.

How do I link tables to SQL without having to use an ODBC driver on every PC?

Thanks

 

[cmmrfrds]

See if this thread helps.

thread705-1081913

You can make the ODBC connection using VBA so that it is not necessary to setup ODBC using the ODBC manager.

 

[Stevehewitt]

Failing that you can create one using registry settings and get the end user to run it - or if you have Visual Studio Tools for Office then you can create the ODBC connection string in the package wizard so it will be created when the user runs the setup.exe package.

Cheers,



Steve.

"They have the internet on computers now!" - Homer Simpson

 

[cmmrfrds]

Where you able to get the tables linked?

Here is a set of functions, three, that find the user tables in the sql server database and link then all in the Access App.


Public Function AddLinkedTablesLooper()
Dim sConnString As String
Dim rs1 As New ADODB.Recordset
Dim tabName As String

' Create a new Table object
Set rs1 = GetSqlServerTables
rs1.MoveFirst
tabName = rs1!Name

While Not rs1.EOF
Call AddSQLServerLinkedTables(tabName)
rs1.MoveNext
tabName = rs1!Name
Wend

Set rs1 = Nothing

End Function

Public Function GetSqlServerTables() As ADODB.Recordset
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset, connString As String
Dim Myarr(50) As String, indx As Integer, maxindx As Integer
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=localhost;" & _
"Persist Security Info=False"

''Set cn = CurrentProject.Connection
cn.ConnectionString = connString
cn.Open
sql1 = "select name from dbo.sysobjects where xtype = 'U'"

rs.Open sql1, cn, adOpenStatic, adLockReadOnly

If rs.EOF And rs.BOF Then
MsgBox "No sql server tables returned"
Set rs = Nothing
Exit Function
End If

rs.MoveFirst
indx = 0
While Not rs.EOF
Myarr(indx) = rs!Name
indx = indx + 1
rs.MoveNext
Wend
maxindx = indx - 1
For indx = 0 To maxindx
Debug.Print "table name = "; Myarr(indx)
Next
Set GetSqlServerTables = rs
Set rs = Nothing
End Function

Public Function AddSQLServerLinkedTables(tabName As String)
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String
On Error GoTo Errhandler
' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={localhost};" & _
"Database=Northwind;" & _
"Uid=sa;" & _
"Pwd=;"

' 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

Set oTable.ParentCatalog = oCat

With oTable
Debug.Print "loop = "; tabName
.Name = "NW_" & tabName
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = tabName
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With
' Add Table object to database

oCat.Tables.Append oTable
oCat.Tables.Refresh

Set oCat = Nothing
Set oTable = Nothing
Exit Function

Errhandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.description & vbCrLf
For Each er In CurrentProject.Connection.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.description
Debug.Print "err source = "; Err.Source
Debug.Print "connection state = "; CurrentProject.Connection.state
Next

End Function

 

[jkirkland]

CMMRFRDS,

I've been looking for a way to create links to all the tables on a SQL Server and it looks like you know what you're doing.

Can you tell me what to put in the connString part of your code:

Public Function GetSqlServerTables() As ADODB.Recordset
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset, connString As String
Dim Myarr(50) As String, indx As Integer, maxindx As Integer
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=localhost;" & _
"Persist Security Info=False"


I don't work with connections very often and the only thing I know about my SQL Server tables is that when linked to access the connection string seems to be like:

ODBC;DSN=Finance;;Table=HTEDTA_GM200DP

From that I can't figure out what to put in the parameters for your code.

Thanks!

 

[PHV]

In ODBC manager have a look at the Finance DSN.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

In my example there are 2 types of connections. The one you show is an OLEDB using ADO connection. This connection is used to get the list of tables from sql server.

connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=localhost;" & _
"Persist Security Info=False"

Depending on what type of authentication you have on sql server many places have NT authentication. The above example uses sql server authentication not NT.

"user id" this is either your NT or sql server login.
"Initial Catalog" this is your database name.
"data source" this is your server name for sql server.
"Persist Security Info=False" this is for not NT auth.
There is also a password which is not shown but you will see it if you do the following.

To test and create the connection string do.

On your desktop.
1. create an empty notepad file and save as text.
2. rename the empty file with the extention .udl
3. the file should turn into an icon. this is the udl wizard.
4. open and work through the wizard to setup your connection.
5. press the test button to check it out.
6. open the file in notepad again. you will see the connection string which you can copy into your program.


Also, there is an ODBC connection to link the tables.

Let me know if you need further assistance.

 

[jkirkland]

Thank you both PHV and cmmrfrds.

I got the connection string using cmmrfrds' suggestion about the .udl file. BTW, that was pretty cool because I had no idea what library to use before going thru those steps.

I get an error message saying "ODBC driver does not support the requested properties" when the GetSqlServerTables() function gets to the following line:

rs.Open sql1, cn, adOpenStatic, adLockReadOnly

Unfortunately I can't figure out where the problem is.

Also, I am assuming that to run this code I can put the following behind a command button:

Private Sub Command0_Click()
Call GetSqlServerTables
Call AddSQLServerLinkedTables(tabName As String)
Call AddLinkedTablesLooper()
End Sub

My only other question (sorry I'm new at this) is what to supply (if anything) for the tabName variable in the function AddSQLServerLinkedTables.

Again, thank you for the help!

 

[cmmrfrds]

The one function calls the other functions and drives the process.

Private Sub Command0_Click()
Call AddLinkedTablesLooper()
End Sub

This is the driving loop.
' Create a new Table object
Set rs1 = GetSqlServerTables
rs1.MoveFirst
This gets the 1st table name
tabName = rs1!Name

While Not rs1.EOF
'- this calls to link the each table.
Call AddSQLServerLinkedTables(tabName)
rs1.MoveNext
'- moves to the next table name.
tabName = rs1!Name
Wend


Can you paste in your connection string. The connection you are referring to should be an OLEDB connection and not be using ODBC. Microsoft has 1 OLEDB driver that goes through ODBC, but it is best to avoid and use the other. I can tell by looking at the connection string.

 

[jkirkland]

The connection string I get with the .udl file is:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;User ID=JKIRKLAN;Data Source=Finance;Initial Catalog=S105VVAM

When I run the code, if I take out:
adOpenKeyset, adLockReadOnly

From: rs.Open sq1, cn, adOpenKeyset, adLockReadOnly

then I get an error saying:
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0204 - SYSOBJECTS in DBO type *FILE not found.

I don't know if that helps identify the issue or not.

Again, I appreciate your help!

 

[cmmrfrds]

adOpenStatic

This is not the problem, but don't use adOpenKeyset for the cursor type. It can be the default on the lock type. see.

rs.Open sq1, cn, adOpenStatic

Go through the wizard again and select the
Microsoft OLEDB Provider for SQL Server and look at what it generates.

The nature of the error suggests that the driver is an IBM DB2 type of driver. The wizard should put
"provider=SQLOLEDB.1;"
in the connection string. This is what you want.

 

[PHV]

Have a look here:

http://www.connectionstrings.com/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

Hfnet, can you show what version of MDAC you have installed.

While in VBA code go up to Tools then References, then which library is checked for
"Microsoft ActiveX Data Objects 2.5 Library"
The number could be 2.1, 2.5, 2.6, 2.7, etc.... This will indicate the version of MDAC. The driver name can be different with the different versions.

The udl wizard will use the library that is installed, that is why I suggested using the wizard.