Importing data from table in external database with password

Occasional Visitor

Please I need help importing data from a table in my backend(UsysTrans) external database using vba sql.  I have been trying not to use linked tables.  I have read and tried code at: https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/insert-in...  among other links.  However have not been successful.

The code below yields "Run-time error '3078':  "The Microsoft Access database engine cannot find the input table or query 'UsysPV_Local'. Make sure it exists and that its name is spelled correctly". 

In a nutshell I need queried data imported from a backend table called UsysTrans (with a field named "batch" into a frontend table named "UsysPV_Local" (also having a field named "Batch"). 

I have been interchanging the table names in the hope the code will work but to no avail.  NB. The location and password data calls work without any problem.  I know because I use them in other parts of the DB.  Hope you can help.

 
Dim dBlOCatION As String
Dim mPV As String, m_Insert, m_Target, m_Fields, m_Source, m_IN, m_Path, uSerLevel
Dim MydB As DAO.Database
Dim wRecdYn As DAO.Recordset
Dim IpassWORD As String

 

IpassWORD = Trim$(pullPwd())  'Getting password from another part of DB
dBlOCatION = BE_Location ' Get Backend database location.

 

myDataStr = INSERT INTO UsysPV_Local  IN "F:\000-DevWork\Storage_be.mdb" SELECT [UsysPV_Local].Batch  FROM [UsysPV_Local]

Set MydB = OpenDatabase(dBlOCatION, False, False, "MS Access;PWD=" & IpassWORD)

 

MydB.Execute myDataStr

 

Will greatly appreciate getting this code to work.

 

 

 

0 Replies