Problem with quotes in SQL INSERT

[Guest]

Hi All,

I have an issue with a SQL string in VBA. The scenario is this:-

A user wishes to import various excel spreadsheets into the same table. xls
files have differing headers and number of columns.

In a form, the user inputs the location and name of a xls file. OnLostFocus
the xls file is linked into the FE with the tablename of tblImpData.

The user then selects from comboboxes where the RowSource is the field names
of tblImpData to provide linking to the correct import fields for
tblRespondent.

On pressing the "Import" button the following code will run (edited for the
sake of repetition)

Private Sub Import_Click()
Dim ab As String
Dim bb As String
Dim strSQL As String
Dim strfields As String
Dim strtbl As String

ab = IIf(Me.cbo74 = "Null", "Null AS Expr1", Me.cbo74.Value)
bb = IIf(Me.cbo76 = "Null", "Null AS Expr2", "tblImpdata." & Me.cbo76.Value)


strfields = "SELECT tblImpdata.'" & ab & "' , '" & bb & "' "

strSQL = "INSERT INTO tblRespondent (Respondent,Company) "

strtbl = " FROM tblImpData;"
Debug.Print strSQL & strfields & strtbl

'DoCmd.RunSQL strSQL & strfields & strtbl

End Sub

The SQl string returns:-
INSERT INTO Respondent (Respondent,Company) SELECT tblImpdata.'Person' ,
'tblImpdata.firm' FROM ImpData;

How do I get rid of the unneed quotes that are fouling the SQL string?
Any help would be appreciated. TIA

Nick.

 

[Guest]

Problem solved. I should have used " instead of '" in the strfields string.
Sometimes we should just get more sleep!!!