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:  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