How to populate local table based on ADODB.Recordset

[khalid]

Hi Friends,
I am breaking my head since 2 days to get populate the local table using ADODB.recordset but can not figure it out.
I have a local table tblLocation_Local which I want to populate/fill with the back-end table tblLocation.

Both the tables have same structures.

I have this code:

' open the connection
OpenCon ' opens the connection - working correctly

Dim rst As ADODB.Recordset ' declare record set

Set rst = New ADODB.Recordset ' set recordset instance

With rst ' open recordset
    .CursorLocation = adUseClient
    .CursorType = adOpenForwardOnly
    .Open ("Select tblLocation.* FROM tblLocation"), Connection
    CurrentDb.Execute ("Insert INTO tblLocation_Local SELECT * FROM tblLocation") ' ---- HERE I NEED THE RECORDSET DATA TO ENTER IN THE LOCAL TABLE (tblLocation_Local)
End With

rst.Close
Set rst = Nothing ' close the recordset
Set Connection = Nothing ' close the connection

On Error GoTo Err
Err_Exit:
Exit Sub
Err:
MsgBox Err.DESCRIPTION
Resume Err_Exit

I need to get tblLocation_Local populated using ADODB.Recordset method so that it will be faster for users to get data from the local (Static) table instead of being connected all the times to the network.

Thanks!

[June7]

Here is how I populate a table with data from another file:
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO PaverData_MajorMRAllYears(SectionID, FAAID) SELECT ID, FAAID FROM [" & strSourcePath & "].zUser_MajorMRAllYears;"
DoCmd.SetWarnings True
Don't need ADODB recordset and connection, the strSourcePath deals with that. I use UNC pathing so the string looks like:
\ \ServerName\path\filename.accdb

[khalid]

Thanks, June! I will give try your suggested code, but my db is password protected and I am wonder if it will work with "strSourcePath"?

[khalid]

Here is how I populate a table with data from another file:
......Don't need ADODB recordset and connection, the strSourcePath deals with that. I use UNC pathing so the string looks like:
\ \ServerName\path\filename.accdb

Thank you very much June, thumbs up, it works like a charms and it didn't asked for password.

Many thanks!

[khalid]

Here is how I populate a table with data from another file:
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO PaverData_MajorMRAllYears(SectionID, FAAID) SELECT ID, FAAID FROM [" & strSourcePath & "].zUser_MajorMRAllYears;"
DoCmd.SetWarnings True
Don't need ADODB recordset and connection, the strSourcePath deals with that. I use UNC pathing so the string looks like:
\ \ServerName\path\filename.accdb

Dim strSourcePath As String
strSourcePath = "C:\01 - Service Contracts-TEST\Service Contracts Back-End.accdb"
DoCmd.RunSQL "INSERT INTO tblSOF SELECT * FROM [" & strSourcePath & "].tblSOF;"

Is says that incorrect password... my backend database is password protected. I am using MS-Access 2010, even the front end application was linked with the back-end database with the correct password but this code is giving me the incorrect password error.