Help with SQL (Access2007)

[Rick]

Hello.

I am trying to integrate data from several sites into 1 (new) table. In
order to distinguish the data from each site in the new table I have a field
(InstID) which holds the Instution number of the site. The fields from the
old site tables and the new table are identical except for the InstID.
InstID and ClientID are Primary Keys.

The path to the old table is asked, then the number for the InstID is asked
and placed as a variable - varInstID.

I have an append sql as follows:

Private Sub UpdateDB_Click()
' populate the clients table
strSql = "INSERT INTO tblClients ( InstID, ClientID, RegistrationNumber,
Active, FirstName, LastName, Address, City, Province, PostalCode, HomePhone,
BirthDate, RegistrationDate) " & vbCrLf & _
"SELECT tblClients.ClientID, tblClients.RegistrationNumber,
tblClients.Active, tblClients.FirstName, tblClients.LastName,
tblClients.Address, tblClients.City, tblClients.Province,
tblClients.PostalCode, tblClients.HomePhone, tblClients.BirthDate,
tblClients.RegistrationDate, * " & vbCrLf & _
"FROM tblClients IN '" & strName & "';"
dbs.Execute (strSql)

.... <snip>

How do I get the varInstID to be placed in the InstID field?

Thanks.

Rick

 

[Rob Parker]

Hi Rick,

You simply need to concatenate the value for the variable varInstID into the
SQL string; specifically, it needs to go into the Select ... From clause in
the first position. If InstID is a number, the line should be:
....
"SELECT " & varInstID & ", tblClients.ClientID,
tblClients.RegistrationNumber,
....

If InstID is a text field, the line should be:
....
"SELECT '" & varInstID & "', tblClients.ClientID,
tblClients.RegistrationNumber,
....
Expanded for clarity, that's:
"SELECT ' " & varInstID & " ', ...


HTH,

Rob

 

[Rick]

Thank you very much Rob. That worked.

I had put the concatenated variable into the Insert Into instead of the
Select clause. Too much time in front of the screen I guess. Duh.

... rick