Import excel spreadsheet to access97 database

[Martin S]

Hi!!
I wonder if it is possible to import data from an excel spreadsheet into
a normalised access97 database with alot of tables in a way that the data
in the spreadsheet will be inserted into the correct tables in the database?
and if so, how? I have been trying to use an ASP-script with not much success.

/Martin

[Paul Clement]

On 1 Aug 2001 06:21:29 -0700, "Martin S" <martinsz@hotmail.com> wrote:

¤
¤ Hi!!
¤ I wonder if it is possible to import data from an excel spreadsheet into
¤ a normalised access97 database with alot of tables in a way that the data
¤ in the spreadsheet will be inserted into the correct tables in the database?
¤ and if so, how? I have been trying to use an ASP-script with not much success.
¤
¤ /Martin

I don't know the structure of your Excel Workbook you can import using SQL and DAO or ADO.

Sub ExportExcelToAccess()

Dim cnn As New ADODB.Connection
Dim sqlString As String

cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=D:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4"

sqlString = "SELECT * INTO [tblExcel] FROM [Excel 8.0;DATABASE=D:\My
Documents\Test.xls].[Sheet1$A37]"

cnn.Execute sqlString

End Sub


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)