Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    8

    Unanswered: Linking Access table in Visual Basic

    Hi

    I am trying to link an external database table into an Access database in Visual Basic.The problem i am facing is ,when i try to link a source table that does not have a primary key , the code hangs up.The reason being is the access opens the unique record identifier dialog box in the background.I need to suppress this unique record identifier property while linking.can anybody help me with this.

    Dim objAccessApp As Access.Application

    Set objAccessApp = New Access.Application
    objAccessApp.Visible = False
    objAccessApp.OpenCurrentDatabase App.Path & "\" & gsDBName, False
    objAccessApp.DoCmd.SetWarnings (0)

    objAccessApp.DoCmd.TransferDatabase acLink, "ODBC Database", _
    "ODBC;DSN=TESTDATAMINER;UID=sa;PWD=erter;LANGUAGE= us_english;DATABASE=Test_DataMiner;", acTable, "EmployeeDetails", "XXX2"


    In the above code since the EmployeeDetails table does not have a primary key,the transferdatabase action hangs up ( internally its actually opening the unique record identifier dialog box)

    Thanks
    -Krithika

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    I think the problem is you NEED a primary key OR index on the SQL table.

    The reason for this error will vary depending on the data provider. In the case of SQL Server OLE DB Provider, this error will occur only because the base table does not have a Primary Key, a Unique Constraint or a Unique Index. With the Jet OLE DB Provider, this error will also occur because the base table does not have a Primary Key or because the Jet Provider cannot determine the Key column(s) (In Access the Primary key MUST be included in the Select statement).
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Nov 2003
    Posts
    8
    Hi SCIROCCO

    I understand that the problem is because of the primary key constraint.
    But i have a requirement to link to a table that does not have a primary key thorough code.

    when we try to do manual linking in a access database to a source table that does not have a primary key , the access shows the 'unique record identifier' dialog.But even when we hit cancel,access performs the linking.

    All i need to know is where to set the option for the access while linking, so that it will work without the primary key constraint.Or is there any options that i can set in the transferdatabase method

    Thanks
    -Krithika

Posting Permissions

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