Appending to a User Level Secured DB

[jachoy]

I'm trying to do an append from one database (source) to a second database (destination) where destination has user level security.

It correctly logs in, via the below text switches, but freezes at the "DoCmd.RunSQL strSQL" command and reports a runtime 3033 about an unauthorized user. Ideally, I'd want to only use a full-data-user ID for the below query, but read on other threads that the below might be considered to be a Modify Design task because the SQL is compiled at runtime. Have tried login/password for an admin, and I don't understand why the security settings still prevent it from running.

 
Function append_external()
Dim strPath As String
Dim strAccDir As String
Dim strAccPath As String
Dim strSQL As String
strAccDir = SysCmd(acSysCmdAccessDir)
strAccPath = strAccDir & "MSACCESS.EXE"
strPath = Chr(34) & strAccPath & Chr(34) & " " & Chr(34) & "<path to destination db>" & Chr(34) & " " & "/wrkgrp " & Chr(34) & "<path to destination db mdw file>" & Chr(34) & " " & "/User " & Chr(34) & "<login>" & Chr(34) & " " & "/Pwd " & Chr(34) & "<password>" & Chr(34)
Shell strPath, vbMaximizedFocus
strSQL = "INSERT INTO [;DATABASE=<path to destination db>].Table1 SELECT * FROM [;DATABASE=<path from source db>].Table1;"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Function

[June7]

A shot in the dark - try connection Execute method instead. I use it to create tables in another file. However, I have never tried anything with password protected file.

Example:
Dim strPath As String
strPath= "your file path\filename.mdb"
CurrentDb.Execute "SELECT * INTO Bituminous IN '" & strPath & "' FROM ConstructionBIT;"
CurrentDb.Execute "SELECT * INTO BituminousMD IN '" & strPath & "' FROM ConstructionBMD;"

Alternatively, try opening a connection to the other database and pulling the data from first database. Example:
Dim A As New Access.Application
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase "path\other filename.mdb", False, "password here"
'my code then does A.Run "GetPaverData" to run a procedure in the connected project. The following might work for you.
A.Execute "INSERT INTO Table1 SELECT * FROM [;DATABASE=<path from source db>].Table1;"

Another example, it opens connection to the backend of split project to create a new table:
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
'I don't know what the provider reference would be for Access 2002
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='path\other filename.mdb'", "userID here", "password here"
'Instead of the CREATE TABLE, try your INSERT
cn.Execute "CREATE TABLE " & Me.tbxTestNum & " (LabNum text(12) PRIMARY KEY Not Null, method text(30) Not Null);"

UserID and password reference added for your consideration, I don't use those arguments.