Sql insert into from external source

[PrincessBretti]

Hi,
I have made an electronic questionnaire for using MS Acess that runs on a laptop and exports data to a floppy disk. I have used a spreadsheet as the output for reasons of size.

I have created another database stored on a network that when the user enters a company record they have the option to import the questionnaire for that company from the floppy disk. i have given the user the option to select the correct questionnaire based upon the company name in a listbox, instead of automating the import, so that if the company name is not exactly the same as on the networked version it does'nt go pearshaped.

However i have a problem:

The questionnaire from the laptop has one primary key but the networked datbase has another (obviously). I wish to import the questionnaire into a table on the networked version, and to store a company ID within that questionnaire data that corresponds to the correct company.

This code will import all of the spreadsheet data but I'm not sure how to add the correct company ID:

'DoCmd.TransferSpreadsheet acImport, 8, "questionnaire", "a:\questionnaire data", 0

this may or may not work cos I'm using a spreadsheet:

'"INSERT INTO "Questionnaire" in "a:\Questionnaire" values select * from "questionnaire" where company_name = & 'lstComp.ItemData(lstComp.ListIndex)'"

but again I need to add the company ID. I know that using a Single-record append that there is an option to input "Values" so this may work.

Any tips or help to point me in the right direction would be wonderful

Thanks

Bretti

 

[kaspi]

Hi
If I understood well, you want to store the new Id on flopy in the excel spredsheet(questionaire).
Well, one of the options would be to create a connection to the spreadsheet trhough ADO and either use the rocordset and find method to locate the record and update the Id or use command object to execute a SQL statement.
Something like -

strDBPath would be the name of your file on a:\
Public Sub OpenExcelDatabase(strDBPath As String)
Dim cnnDB As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnnDB = New ADODB.Connection

'I use named ranges for accessing a data on a sheet - in this case called "rs"
With cnnDB
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0"
.Open strDBPath
Debug.Print .ConnectionString
Set rs = New ADODB.Recordset
rs.ActiveConnection = cnnDB
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Open "rs"
' code to find and update record with the value
.Close
End With

Set cnnDB = Nothing
End Sub

Hope it helps
kaspi