none
Create link to Access Table Using ADO RRS feed

  • Question

  • I am trying to link a table from one access database to another access database; I have deployed my project on about 30 PCs and receive an error on 3 of them. I use Visual Studio 2005 to develop. Other people have suggested the problem is probably an ODBC driver issue, since I use the same connection string in other places in my project and everything is fine. I have compared the entries in the Control Panel - admin tools - ODBC Data Source Administrator and also the registry and they were the same as a good install. I have also ran a trace. Any help would be greatly appreciated

    See below for

    ADO link code

    Error Message

    ODBC trace log

    Connection String code

     

    ADO link code 

    Code Block

    1.    Private Sub LinkZipCodeTable()

    2.     Dim adoCn As New ADODB.Connection

    3.     Dim adoCat As New ADOX.Catalog

    4.     Dim adoTbl As New ADOX.Table

    5.   

    6.     adoCn.Open(g_DDB)

    7.     adoCat.ActiveConnection = adoCn

    8.     adoTbl.ParentCatalog = adoCat

    9.     adoTbl.Name = "Zips"

    10.     adoTbl.Properties("Jet OLEDB:Link Datasource").Value = _

    11.     g_DBDirectory & "\ZIPUSA.mdb"

    12.     adoTbl.Properties("Jet OLEDB:Link Provider String").Value = "MS Access"

    13.     adoTbl.Properties("Jet OLEDB:Remote Table Name").Value = "Zips"

    14.     adoTbl.Properties("Jet OLEDB:Create Link").Value = True

    15.     Try

    16.     adoCat.Tables.Append(adoTbl)

    17.     Catch e As System.Runtime.InteropServices.COMException

    18.     If e.Message <> "Object 'Zips' already exists." Then

    19.     MsgBox(e.Message)

    20.     End If

    21.     End Try

    22.     adoCn.Close()

     

     

     

    Error Message 

    Code Block

    System.Runtime.InteropServices.COMException (0x80004005): [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options

     

     

     

    ODBC trace log 

    Code Block

    PQ90 644-5c4 ENTER SQLAllocEnv

    HENV * 05DDF6B0

     

    PQ90 644-5c4 EXIT SQLAllocEnv with return code 0 (SQL_SUCCESS)

    HENV * 0x05DDF6B0 ( 0x05df1540)

     

    PQ90 644-5c4 ENTER SQLGetEnvAttr

    SQLHENV 05DF1540

    SQLINTEGER 201

    SQLPOINTER [Unknown attribute 201]

    SQLINTEGER 4

    SQLINTEGER * 0x0012D640

     

    PQ90 644-5c4 EXIT SQLGetEnvAttr with return code 0 (SQL_SUCCESS)

    SQLHENV 05DF1540

    SQLINTEGER 201

    SQLPOINTER [Unknown attribute 201]

    SQLINTEGER 4

    SQLINTEGER * 0x0012D640 (68630592)

     

    PQ90 644-5c4 ENTER SQLAllocEnv

    HENV * 05DDF6B4

     

    PQ90 644-5c4 EXIT SQLAllocEnv with return code 0 (SQL_SUCCESS)

    HENV * 0x05DDF6B4 ( 0x05df15e8)

     

    PQ90 644-5c4 ENTER SQLSetEnvAttr

    SQLHENV 05DF15E8

    SQLINTEGER 201

    SQLPOINTER [Unknown attribute 201]

    SQLINTEGER -6

     

    PQ90 644-5c4 EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)

    SQLHENV 05DF15E8

    SQLINTEGER 201

    SQLPOINTER [Unknown attribute 201]

    SQLINTEGER -6

     

    PQ90 644-5c4 ENTER SQLAllocConnect

    HENV 05DF15E8

    HDBC * 0012E23C

     

    PQ90 644-5c4 EXIT SQLAllocConnect with return code 0 (SQL_SUCCESS)

    HENV 05DF15E8

    HDBC * 0x0012E23C ( 0x05df1690)

     

    PQ90 644-5c4 ENTER SQLGetInfoW

    HDBC 05DF1690

    UWORD 10

    PTR 0x0012E208

    SWORD 22

    SWORD * 0x00000000

     

    PQ90 644-5c4 EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)

    HDBC 05DF1690

    UWORD 10

    PTR 0x0012E208

    SWORD 22

    SWORD * 0x00000000

     

    PQ90 644-5c4 ENTER SQLSetConnectAttrW

    SQLHDBC 05DF1690

    SQLINTEGER 103

    SQLPOINTER 0x0000000F

    SQLINTEGER -6

     

    PQ90 644-5c4 EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS)

    SQLHDBC 05DF1690

    SQLINTEGER 103

    SQLPOINTER 0x0000000F (BADMEM)

    SQLINTEGER -6

     

    PQ90 644-5c4 ENTER SQLDriverConnectW

    HDBC 05DF1690

    HWND 00000000

    WCHAR * 0x74329A38 [ -3] "******\ 0"

    SWORD -3

    WCHAR * 0x74329A38

    SWORD 2

    SWORD * 0x00000000

    UWORD 0

     

    PQ90 644-5c4 EXIT SQLDriverConnectW with return code -1 (SQL_ERROR)

    HDBC 05DF1690

    HWND 00000000

    WCHAR * 0x74329A38 [ -3] "******\ 0"

    SWORD -3

    WCHAR * 0x74329A38

    SWORD 2

    SWORD * 0x00000000

    UWORD 0

     

    DIAG [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0)

     

    PQ90 644-5c4 ENTER SQLGetDiagRecW

    SQLSMALLINT 2

    SQLHANDLE 05DF1690

    SQLSMALLINT 1

    SQLWCHAR * 0x0012E19C (NYI)

    SQLINTEGER * 0x0012DD80

    SQLWCHAR * 0x0012DD9C (NYI)

    SQLSMALLINT 512

    SQLSMALLINT * 0x0012DD84

     

    PQ90 644-5c4 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS)

    SQLSMALLINT 2

    SQLHANDLE 05DF1690

    SQLSMALLINT 1

    SQLWCHAR * 0x0012E19C (NYI)

    SQLINTEGER * 0x0012DD80 (0)

    SQLWCHAR * 0x0012DD9C (NYI)

    SQLSMALLINT 512

    SQLSMALLINT * 0x0012DD84 (91)

     

    PQ90 644-5c4 ENTER SQLGetDiagRecW

    SQLSMALLINT 2

    SQLHANDLE 05DF1690

    SQLSMALLINT 2

    SQLWCHAR * 0x0012E19C (NYI)

    SQLINTEGER * 0x0012DD80

    SQLWCHAR * 0x0012DD9C (NYI)

    SQLSMALLINT 512

    SQLSMALLINT * 0x0012DD84

     

    PQ90 644-5c4 EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)

    SQLSMALLINT 2

    SQLHANDLE 05DF1690

    SQLSMALLINT 2

    SQLWCHAR * 0x0012E19C (NYI)

    SQLINTEGER * 0x0012DD80

    SQLWCHAR * 0x0012DD9C (NYI)

    SQLSMALLINT 512

    SQLSMALLINT * 0x0012DD84

     

    PQ90 644-5c4 ENTER SQLFreeConnect

    HDBC 05DF1690

     

    PQ90 644-5c4 EXIT SQLFreeConnect with return code 0 (SQL_SUCCESS)

    HDBC 05DF1690

     

     

     

    Connection String code 

    Code Block

    Public Function BuildConnectionString(ByVal FileName As String, Optional ByVal UsePassword As Boolean = False) As String

            Dim password As String

     

            If g_DeBug Or Not UsePassword Then

                password = ""

            Else

                password = "xxxxxxx"

            End If

            ' "Jet OLEDB:New Database Password='';" & _

            'BuildConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Password='';" & _

            BuildConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

                                    "User ID=Admin;Data Source=" & FileName & _

     

     

     

     

    Thursday, October 18, 2007 7:17 PM

All replies