Forum Discussion
Importing data from table in external database with password
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-into-statement-microsoft-access-sql 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 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.
