Query for importing data from Excel

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

At the moment I use the following query:

INSERT INTO Table1 ( Field1, Field2)
SELECT Field1, Field2
FROM [Sheet$] IN 'C:\File.xls'[EXCEL 5.0;];

The above works fine. But I need the path to be taken from
a field on a form (e.g. from [Forms]![frmMyForm]!
[FilePath] )

What will be the syntax instead of " 'C:\File.xls' "?

Thank you in advance!
 
Mike said:
At the moment I use the following query:

INSERT INTO Table1 ( Field1, Field2)
SELECT Field1, Field2
FROM [Sheet$] IN 'C:\File.xls'[EXCEL 5.0;];

The above works fine. But I need the path to be taken from
a field on a form (e.g. from [Forms]![frmMyForm]!
[FilePath] )

What will be the syntax instead of " 'C:\File.xls' "?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have to dynamically create the SQL statement using code. E.g.:

dim strSQL as string
strSQL = "INSERT INTO Table1 ( Field1, Field2) " & _
"SELECT Field1, Field2 " & _
"FROM [Sheet$] IN " & Forms!frmMyForm!FilePath

' Then execute the string
CurrentDB.Execute strSQL, dbFailOnError

If you are runing this query from frmMyForm the reference to the control
would only be:

Me!FilePath

instead of the complete Forms!... declaration.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQF416IechKqOuFEgEQIkygCeO7kCCiSpwa5L2zX0DrxXDvVhafUAoIe7
OtOJtfkcVDkJl05l0SfmPI4L
=abXJ
-----END PGP SIGNATURE-----
 
Back
Top