Pass-Through Query with Parameters from a Table

[RoyHobbs]

Hi there,

I can construct a pass-through query to access my accounting software's
database using odbc, but I have to type in the connection string parameters
such as username, password, DSN name etc to the ODBC Connect Str of the Query
Properties - for each query.

What I would like to do is have the connection string parameters (username,
password...) for the pass-through query pulled from a Table in the same mdb.
In other words, have a table with the connection string parameters then use
VBA to build the pass-through query with the connection string based on the
table supplied parameters.

I've struggled with the VBA to do this and cannot get it right. I don't have
a lot of experience with VBA.

Can anyone provide me with some VBA that will create a pass-through query
pulling the ODBC connection string parameters from a Table?

Thanks in advance.

 

[Terry Kreft]

A connection string is simply pairs of key/values where the key and value
are separated with an equals sign and pairs of key/values are semicolon
separated, so it's fairly easy to parse and construct.

If you make your table (tConnect) like this

Key Text (255)
Value Text (255)

You can then just fill it in e.g.

Call FillConnect and pass a valid connection string:-
Sub FillConnect(strConnect As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varPairs As Variant
Dim varPair As Variant
Dim intPairs As Integer

varPairs = Split(strConnect, ";")

Set db = CurrentDb

db.Execute "DELETE * FROM tConnect"

Set rs = db.OpenRecordset("Select Key, Value From tConnect",
dbOpenDynaset)

For intPairs = LBound(varPairs) To UBound(varPairs)
varPair = Split(varPairs(intPairs), "=")
On Error Resume Next
With rs
.AddNew
.Fields(0) = varPair(0)
.Fields(1) = varPair(1)
.Update
End With
Next

rs.Close
Set rs = Nothing
Set db = Nothing
Erase varPair
Erase varPairs
End Sub

To retrieve the connection string just call ConnectFromTable

Function ConnectFromTable() As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strRet As String

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT * FROM tConnect WHERE Key <> 'ODBC'",
dbOpenDynaset)

strRet = "ODBC"
With rs
Do Until .EOF
If Len(.Fields(1) & "") < 1 Then
strRet = strRet & ";" & .Fields(0) & "="""""
Else
strRet = strRet & ";" & .Fields(0) & "=" & .Fields(1)
End If
.MoveNext
Loop
End With

rs.Close
Set rs = Nothing
Set db = Nothing

ConnectFromTable = strRet
End Function


Note: both the functions above were written for this specific post therefoe
they have not been tested with connetion strings from all providers, in fact
thy have onl been tested with a SQL Server string.