Importing data from table in external database with password

%3CLINGO-SUB%20id%3D%22lingo-sub-1601685%22%20slang%3D%22en-US%22%3EImporting%20data%20from%20table%20in%20external%20database%20with%20password%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1601685%22%20slang%3D%22en-US%22%3E%3CP%3EPlease%20I%20need%20help%20importing%20data%20from%20a%20table%20in%20my%20backend(UsysTrans)%20external%20database%20using%20vba%20sql.%26nbsp%3B%20I%20have%20been%20trying%20not%20to%20use%20linked%20tables.%26nbsp%3B%20I%20have%20read%20and%20tried%20code%20at%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fclient-developer%2Faccess%2Fdesktop-database-reference%2Finsert-into-statement-microsoft-access-sql%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice%2Fclient-developer%2Faccess%2Fdesktop-database-reference%2Finsert-into-statement-microsoft-access-sql%3C%2FA%3E%26nbsp%3B%20among%20other%20links.%26nbsp%3B%20However%20have%20not%20been%20successful.%3C%2FP%3E%3CP%3EThe%20code%20below%20yields%20%22Run-time%20error%20'3078'%3A%26nbsp%3B%20%22The%20Microsoft%20Access%20database%20engine%20cannot%20find%20the%20input%20table%20or%20query%20'UsysPV_Local'.%20Make%20sure%20it%20exists%20and%20that%20its%20name%20is%20spelled%20correctly%22.%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20a%20nutshell%20I%20need%20queried%20data%20imported%20from%20a%20backend%20table%20called%20UsysTrans%20(with%20a%20field%20named%20%22batch%22%20into%20a%20frontend%20table%20named%20%22UsysPV_Local%22%20(also%20having%20a%20field%20named%20%22Batch%22).%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20interchanging%20the%20table%20names%20in%20the%20hope%20the%20code%20will%20work%20but%20to%20no%20avail.%26nbsp%3B%20NB.%20The%20location%20and%20password%20data%20calls%20work%20without%20any%20problem.%26nbsp%3B%20I%20know%20because%20I%20use%20them%20in%20other%20parts%20of%20the%20DB.%26nbsp%3B%20Hope%20you%20can%20help.%3C%2FP%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CFONT%3EDim%20dBlOCatION%20As%20String%3CBR%20%2F%3EDim%20mPV%20As%20String%2C%20m_Insert%2C%20m_Target%2C%20m_Fields%2C%20m_Source%2C%20m_IN%2C%20m_Path%2C%20uSerLevel%3CBR%20%2F%3EDim%20MydB%20As%20DAO.Database%3CBR%20%2F%3EDim%20wRecdYn%20As%20DAO.Recordset%3CBR%20%2F%3EDim%20IpassWORD%20As%20String%3C%2FFONT%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EIpassWORD%20%3D%20Trim%24(pullPwd())%26nbsp%3B%20'Getting%20password%20from%20another%20part%20of%20DB%3CBR%20%2F%3EdBlOCatION%20%3D%20BE_Location%20'%20Get%20Backend%20database%20location.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EmyDataStr%20%3D%26nbsp%3B%3CFONT%3EINSERT%20INTO%20UsysPV_Local%26nbsp%3B%20IN%20%22F%3A%5C000-DevWork%5CStorage_be.mdb%22%20SELECT%20%5BUsysPV_Local%5D.Batch%26nbsp%3B%20FROM%20%5BUsysPV_Local%5D%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3ESet%20MydB%20%3D%20OpenDatabase(dBlOCatION%2C%20False%2C%20False%2C%20%22MS%20Access%3BPWD%3D%22%20%26amp%3B%20IpassWORD)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EMydB%3C%2FFONT%3E.Execute%20myDataStr%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWill%20greatly%20appreciate%20getting%20this%20code%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1601685%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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