Insert Data From Remote Access Database to a Local Access Database (1 Viewer)

[twcaddell]

I have a requirement to display in a subform data from remote tables in an access database (900 miles away).

I do not want to link the tables because one table is quite large, the time to connect lengthens time my system is ready, I only need a subset of data (a month at a time) and I only need the data once a month for a series of reports.

I have created a local table that matches the dataset wanted.
I have developed a SQL query, open the database using the OpenDatabase method and can return the data as a recordset. I know I can step through the recordset one record at a time and do an insert of that record into the local database, but (while I haven't tested it) it seems that will be slow.

Here is my SQL Statement

strSQL = "SELECT ApplicationCodes.App, ChapterTbl.ChapterName, OutputFilesTbl.FileIdent, " & _
        "OutputFilesTbl.OutPutTitle, FileDataTbl.StatementDate, FileDataTbl.DateReleased, " & _
        "FileDataTbl.TotalImages " & _
        "FROM FileDataTbl " & _
        "INNER JOIN (ApplicationCodes " & _
        "INNER JOIN (ChapterTbl INNER JOIN OutputFilesTbl " & _
        "ON ChapterTbl.ChapterID = OutputFilesTbl.pf_ChptID) " & _
        "ON ApplicationCodes.APPID = ChapterTbl.pf_APPID) " & _
        "ON FileDataTbl.pf_OutPutID = OutputFilesTbl.OutPutID " & _
        "WHERE (FileDataTbl.StatementDate Between #" & Me.BDate & "# And #" & Me.EDate & "#) " & _
        "ORDER BY ApplicationCodes.App, ChapterTbl.ChapterName;"
    Debug.Print strSQL
    Set rst = HinesdB.OpenRecordset(strSQL)

Is there anyway to do a bulk insert using an insert statement like:

INSERT INTO LocalTable (App, ChapterName, FileIdent, StatementDate,
     DateReleased, TotalImages)
     SELECT <using a variation of above>

I know this can be done with tables residing in the same database, but the remote aspect as well as the inner joins has me stumped and not sure it can even be done.

Any ideas would be greatly appreciated.

Thanks
TC

 

[jdraw]

How do you plan to get the data from the database 900 miles away?
What sort of communications link etc???
How will you keep the data "in sync"?

 

[twcaddell]

Hi
The remote data is the authoratative data and will not be updated by the local database calling for the data. The local data (read-only) is used for reviewing the data on line and compiling reports. Reports are always a month out (January's report created end of month uses Dec Data)

Not sure I understand the question on getting the data, so via the transport layer and opening the remote database with the opendatabase method

TC

 

[CJ_London]

Is there anyway to do a bulk insert using an insert statement like:

Code:
INSERT INTO LocalTable (App, ChapterName, FileIdent, StatementDate, DateReleased, TotalImages) SELECT <using a variation of above>

yes - if you have a linked table, it is treated the same as a local table.

But it sounds like you are looking for a way of interrogating a distant access database quicker than using a linked table?

The only other option I can think of is to have a dsn less connection

the connection string depends on the version of access, security in play etc but here is a link to the options

http://www.connectionstrings.com/access/

Your sql would be something like

SELECT * FROM farTable in dbpath & connection string

The bit in red is the equivalent of just a table name as you would use in an update or append or whatever type query. Regret I can't remember whether or not you need to use round or square brackets, you'll need to experiment

However I don't know if this would be any quicker than a linked table