Create table in one database using data from another

[RichardH]

Hi,
I'm new to this Access ,SQL and VBA world so would appreciate some help.
I have a database currently sitting on a shared drive, and I am accessing it from a local version on my c drive. I need to create a table on startup that takes data from the main database for use on the local version.
How do I fix the following SQL statement to take the data from one database and create a new table in the other?

Dim strSQL As String

strSQL = "SELECT tblInitiativeDetails.txtProjectName, tblInitiativeDetails.txtSource, "
strSQL = strSQL + "tblInitiativeDetails.txtCategory, tblInitiativeDetails.txtMasterBrand, "
strSQL = strSQL + "tblInitiativeDetails.txtBrand, tblInitiativeDetails.txtSubBrand, "
strSQL = strSQL + "tblInitiativeDetails.txtInitType, tblInitiativeDetails.txtInitDesc, "
strSQL = strSQL + "tblInitiativeDetails.txtAdditInfo, tblInitDates.txtDay, "
strSQL = strSQL + "tblInitDates.txtMonth, tblInitDates.txtYear, tblInitStatus.txtStatus, "
strSQL = strSQL + "tblInitStatus.txtProjectLevel, tblInitCountries.txtCountry, "
strSQL = strSQL + "tblInitCountries.txtArea, tblInitCountries.chkLeadCountry, "
strSQL = strSQL + "tblInitiativeDetails.txtLastChangedBy, tblInitiativeDetails.MasterId, "
strSQL = strSQL + "tblInitiativeDetails.txtInitId IN ('L:\WECR\01 Initiative Tracker\Database\Initiatives Tracker.mdb') "
strSQL = strSQL + "INTO tblUserInits "
strSQL = strSQL + "FROM tblInitType INNER JOIN (((tblInitiativeDetails INNER JOIN "
strSQL = strSQL + "tblInitCountries ON tblInitiativeDetails.txtInitId=tblInitCountries.txtInitId) "
strSQL = strSQL + "INNER JOIN tblInitDates ON tblInitiativeDetails.txtInitId=tblInitDates.txtInitId) "
strSQL = strSQL + "INNER JOIN tblInitStatus ON tblInitiativeDetails.txtInitId=tblInitStatus.txtInitId) "
strSQL = strSQL + "ON tblInitType.txtInitType=tblInitiativeDetails.txtInitType IN ('L:\WECR\01 Initiative Tracker\Database\Initiatives Tracker.mdb') "
strSQL = strSQL + "WHERE (((tblInitiativeDetails.txtLastChangedBy)=Forms!frmInitiativesTracker!txtUserName));"

DoCmd.RunSQL strSQL

The SELECT part needs to refer to the L drive database, the INTO part needs to create the table on the C drive, and the FROM needs to refer to the L drive.

I apologise if it's a stupid question but I'm new to this. Thanks for any help you can give me.

 

[Bat17]

Link any tables you need and just run a query. no need to duplicate data by saving it to your hard drive

Peter

 

[RichardH]

Speed issue

Thanks for the reply. The reason I'm doing it like this is because I'll have a small number of people across europe using this and it's really slow (unusable) if I leave everything on the L drive. Hence moving the copies to the C drive to refer to, before updating the L drive with changes.

 

[RichardH]

Anyone

Can anyone tell me what's wrong with the sql statement? Is this actually possible or not?