Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: transferring data from 1 db to another

    I am trying to open a database for a recordset in MS Access97. I have used the following code:

    Dim firstdb As Database
    Dim qryget As String
    Dim getdata As Recordset

    Set firstdb = OpenDatabase("d:\PC_Dbase\PersComp.mdb")
    qryget = "select PCs from firstdb"
    Set getdata = firstdb.OpenRecordset(qryget)

    and I get the following error;
    The Microsoft Jet database engine cannot find the input table or query firstdb. Make sure it exists and that its name is spelled correctly. (Error 3078).

    I used to get this error in VB, so I inserted a datacontrol, then removed it and the code worked.

    Basically I am trying to open a database, load some fields into a recordset and then save them to another database.

    Is this the only way to do it, or is there a simpler way to transfer the data. Keep in mind that the field names in the 2 databases are different.

    Any help would be appreciated.

    Thanks.

  2. #2
    Join Date
    Oct 2003
    Location
    India
    Posts
    55

    Data Transfer

    Hi
    Why dont you check with

    docmd.transferdatabase

    it is very easy method to trnsfer table queries and other database objects between databases

    check it out

    regards

    sreeraj
    Sree

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You can use
    DoCmd.OutputTo acOutputTable, yourTable, acFormatXLS, yourFileName2bExportedWithFullPath & ".xls"

    This will export the table from your db to Excel file.


    Then Open your Secound db and use

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, yourNewTableName, yourFileName2bImportedWithFullPath

    This will export the datat in the Excel to a new table

    Then you have to change the Fields' names to be as in the secound db, you can use a query to do that ()
    using the AS patr of Select.

  4. #4
    Join Date
    Nov 2003
    Location
    Warren, PA
    Posts
    52
    In the VBA window under tools then references make sure Microsoft DAO 3.51 Object Library is checked and above Microsoft ActiveX Data Object 2.5 Library this should work

    Brent

  5. #5
    Join Date
    Dec 2003
    Posts
    2
    Thanks for the advice.

Posting Permissions

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