Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    Hollywood, FL
    Posts
    5

    Unanswered: TransferDatabase - ODBC - DSN as string

    I’m trying to link tables in MS Access using a variant as the DSN name but it is not working.

    In the following:
    C PROJECTS is the Name of the System DSN that was setup using the ODBC Data Source Administrator

    PWD is a string variant set by using:
    PWD = Forms!Lk_Form![User Name]

    PW is a string variant set by using
    PW = Forms!Lk_Form![User PassWord]

    MainTable is a string variant set by using
    MainTable = Left(Forms!Lk_Form![GetTableName],4)


    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=C PROJECTS;UID=User;PWD=PW;OPENMODE=1;ACCESSFRIENDLY =YES", acTable, MainTable & "ACC", "LinkCurrentACC"

    The above works but I want to be able to have the user use different ‘System DSN’ that were set up using the ODBC Data Source Administrator


    In the following:

    C PROJECTS is the Name of the System DSN that was setup using the ODBC Data Source Administrator

    My_Sys_DSN is a string variant set by using
    My_Sys_DSN = Forms!Lk_Form![CurrentDSN]
    This results in My_Sys_DSN = C PROJECTS
    I have check this.

    PWD is a string variant set by using:
    PWD = Forms!Lk_Form![User Name]

    PW is a string variant set by using
    PW = Forms!Lk_Form![User PassWord]

    MainTable is a string variant set by using
    MainTable = Left(Forms!Lk_Form![GetTableName],4)


    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=My_Sys_DSN;UID=User;PWD=PW;OPENMODE=1;AC CESSFRIENDLY=YES", acTable, MainTable & "ACT", "LinkCurrentACT"

    But with the above I get “ODBC—connection to ‘My_Sys_DSN’ failed.” How can I use a string variant as the DSN Name? Thank you.
    Gene

  2. #2
    Join Date
    Sep 2003
    Posts
    19

    Re: TransferDatabase - ODBC - DSN as string

    try DSN=" & My_Sys_DSN & "....
    otherwise it thinks your dsn is actually called my_sys_dsn

    Originally posted by RRStocks
    I’m trying to link tables in MS Access using a variant as the DSN name but it is not working.

    In the following:
    C PROJECTS is the Name of the System DSN that was setup using the ODBC Data Source Administrator

    PWD is a string variant set by using:
    PWD = Forms!Lk_Form![User Name]

    PW is a string variant set by using
    PW = Forms!Lk_Form![User PassWord]

    MainTable is a string variant set by using
    MainTable = Left(Forms!Lk_Form![GetTableName],4)


    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=C PROJECTS;UID=User;PWD=PW;OPENMODE=1;ACCESSFRIENDLY =YES", acTable, MainTable & "ACC", "LinkCurrentACC"

    The above works but I want to be able to have the user use different ‘System DSN’ that were set up using the ODBC Data Source Administrator


    In the following:

    C PROJECTS is the Name of the System DSN that was setup using the ODBC Data Source Administrator

    My_Sys_DSN is a string variant set by using
    My_Sys_DSN = Forms!Lk_Form![CurrentDSN]
    This results in My_Sys_DSN = C PROJECTS
    I have check this.

    PWD is a string variant set by using:
    PWD = Forms!Lk_Form![User Name]

    PW is a string variant set by using
    PW = Forms!Lk_Form![User PassWord]

    MainTable is a string variant set by using
    MainTable = Left(Forms!Lk_Form![GetTableName],4)


    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=My_Sys_DSN;UID=User;PWD=PW;OPENMODE=1;AC CESSFRIENDLY=YES", acTable, MainTable & "ACT", "LinkCurrentACT"

    But with the above I get “ODBC—connection to ‘My_Sys_DSN’ failed.” How can I use a string variant as the DSN Name? Thank you.
    Gene

  3. #3
    Join Date
    Sep 2003
    Location
    Hollywood, FL
    Posts
    5

    Cool

    No that will not work I have tried that and much more and after much trial and error this is what you need to do to get to work:


    First set the variable
    My_Sys_DSN = "DSN=" & Trim(Forms!Lk_Form!CurrentDSN) & ";UID=" & User & ";PWD=" & PW & " ;OPENMODE=1;ACCESSFRIENDLY=YES"

    Then

    DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;” & My_Sys_DSN, acTable, MainTable & "ACT", "LinkCurrentACT"

    I just keep trying different things until it worked. I think TransferDatabase acLink, "ODBC Database" must have the “ODBC:” to know what it’s trying to link to then tell it , with a variable, the connection string. Thank you.

    Gene

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •