Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Pass-Through Query with Parameters from a Table

Discussion in 'Information Technology' started by RoyHobbs, Jul 28, 2006.

  1. RoyHobbs

    RoyHobbs
    Expand Collapse
    Guest

    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.
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    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.


    --

    Terry Kreft


    "RoyHobbs" <RoyHobbs@discussions.microsoft.com> wrote in message
    news:06FEBEFF-26E9-4B46-AA8C-2572226D3CF0@microsoft.com...
    > 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.
    >
    >
     

Share This Page