View Single Post
  #4 (permalink)  
Old July 11th, 2007, 04:35 AM
robzyc robzyc is offline
Friend of Wrox
 
Join Date: Mar 2007
Location: Hampshire, United Kingdom.
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jez,

No problem. When you Use DoCmd, in most cases it will always work with the Current database, this, in your case is the Front End. So yes, when you import the spreadsheet, it will be placed into the Front End.

In order to Append the records in the Front End, you will need to link to that in some way.
If the Address/Location of the Backend Database is static then you could use:
INSERT INTO [TableName] IN 'C:\SomeDir\AccessDB.mdb'
SELECT [Field 1], [Field2]


And this should work.

So something like:
Code:
Dim sql As String
sql = _
"INSERT INTO tblCSATAddressA IN 'C:\DB.mdb'" & vbCrLf & _
    "( JobNumber, Address, ProjectID, Project, JobDate, TeamCode, Engineer, Contract, BusinessType )" & vbCrlf & _
"SELECT" & vbcrlf & _
    "tblCSATAddressTEMP.[No]," & vbCrLf & _
    "tblCSATAddressTEMP.Description," & vbCrlf & _
    "tblCSATAddressTEMP.[Bill-to Customer No]," & vbCrLf & _
    "tblCSATAddressTEMP.[Scheme Code]," & vbCrLf & _
    "tblCSATAddressTEMP.[Planned Start Date]," & vbCrLf & _
    "tblCSATAddressTEMP.[Team Code]," & vbCrLf & _
    "tblFamilyTree.Engineer," & vbCrLf & _
    "tblFamilyTree.Contract," & vbCrLf & _
    "'" & Me.cboBusinessType & "' AS BusinessType" & vbCrLf & _
"FROM tblCSATAddressTEMP" & vbCrlLf & _
"LEFT JOIN tblFamilyTree ON tblCSATAddressTEMP.[Team Code] = tblFamilyTree.TeamCode"
CurrentProject.Connection.Execute
Be sure to update the address of the DB highlighted in bold!!

Hope this helps/points you in the right direction.

Regards,
Rob

Reply With Quote