Using INSERT INTO from external database

[Williams485]

Hello,

I'm trying to get data from a number of external dbase iV (I know!) databases and centralise it in a single Access table for reporting purposes. I'm trying to use the following query:

INSERT ondate, srectype, actvcode, resultcode
INTO [TempStats] FROM extTable IN 'g:\databases\extranet\tabledir\' 'dBASE IV;' 
WHERE ondate = #02/16/2015#

I get a runtime error when trying to execute the code (saying there's a syntax error in the INSERT INTO statement).

Is what I'm trying to do possible (I can achieve it using SELECT INTO, but don't want the data in several tables), what is the correct syntax (I can't seem to find a resource online), or is there a better way (I'm trying to avoid using recordsets and looping through records)?

Any help I get will be much appreciated!

[JoeM]

I would run it from the database where you want the data to be put in. Just link the table where the data is coming from in the database, and then write your Append Query there.

[Williams485]

Thanks for the quick response, JoeM. However, that isn't an option: the database is the back end of our CRM, which doesn't give us the flexibility; linking the tables significantly affects performance.

If it's not possible, I may create a new Access table for each external database, then append them into a final table. Unless, you think there's a better way?

[JoeM]

I am not quite sure I fully understand your environment and exactly where you are trying to put the data.

So, you have a bunch of dbase files, and one Access database? Is that right?
And you are trying to copy information from these dbase files to your one Access database?
If that is the case, I am not sure exactly where you are trying to run your INSERT INTO query from. Is there another database involved?
If you have an Access database where you are just running the queries, can you link the Access table from your "final" Access database to here?

Another option would be to have your program export all the records to some sort of file, and set-up a process so that your "final" database goes out to a folder every day and automatically loads all files in that folder.

[ssanfu]

I was not able to get your query to work, but I do have a work around of sorts.

So the idea is to open a recordset on the dbf file and use an append query to add the records to an Access table.

Public Sub FromDB_IV()
    Dim d As DAO.Database
    Dim rsSource As DAO.Recordset

    Dim sSQL As String

    Set d = CurrentDb

    'open the dbf source file
    sSQL = "SELECT billid, bName,bCity,bZip"
    sSQL = sSQL & " FROM Client"        '<=name of the dbf file
    sSQL = sSQL & " IN '' [dBASE IV; Database=F:\MyTest\] ;"
    '          Debug.Print sSQL

    'my Client dbf file has ~ 20 fields
    'I only selected 4 fields, all text fields.
    ' Note: Could not get a WHERE clause to work.

    Set rsSource = d.OpenRecordset(sSQL)
    If Not (rsSource.BOF And rsSource.EOF) Then
        rsSource.MoveLast
        rsSource.MoveFirst

        'code to insert each record from dbf file into Access table
        Do While Not rsSource.EOF
            'build the SQL append query
            sSQL = "INSERT INTO Client_Acc (billid, bName, bCity, bZip)"
            sSQL = sSQL & " VALUES ('" & rsSource("billid") & "', '" & rsSource("bName") & "', '" & rsSource("bCity") & "', '" & rsSource("bZip") & "')"
            '       Debug.Print sSQL

            d.Execute sSQL, dbFailOnError
            rsSource.MoveNext
        Loop


    End If
    MsgBox "Done"
    rsSource.Close
    Set rsSource = Nothing
    Set d = Nothing

End Sub

Append to a temp table, then add the records you want (the where clause) to the "real" table.

Note that there is no error handling code.

[Williams485]

Sorry, JoeM. I'll try to break it down.

Our CRM system has a number of distinct databases, each with identical table relationships (some tables have different fields). What my code is trying to do is loop through each database root folder, extract a selection of records and insert them into a table in the Access database (from which I am running the query and intend to build the reports, etc).

So, iterating through a list of the database directories, I programmatically build a query that accesses the 'conthist' table, selects records and inserts them into my Access table.

Does that make more sense?

[Williams485]

Thanks for your response, SSanfu. I will bear your suggestion in mind. However, I need to be extracting the data on a regular basis and need to avoid, where possible copying individual records one at a time--to save time.