Transferring Large Amount of Data from Excel 2003 to Access 2003

  • Thread starter Thread starter SSweez
  • Start date Start date
S

SSweez

I have about 1,000 to 1,500 records in Excle 2003 that I am trying to
get into Access 2003 using VBA the fasted way possible. Right now I
have an INSERT query that loops through the records but this is very
slow. I have read about some other options that would be faster like
creating a linked table to this data and then I can append the data and

destroy the linked table. However, I think this functionality was lost

in Excel 2003 but I am not sure. Can anyone help with what the best
solution is and some sample code (or a reference on the web I can get
to). Thanks in advance.

-Scott
 
Thanks. I am gong to try this right now. Is this the fasted way (in
terms of performance) to get large amounts of data from Excel into
Access?
 
I need to trigger the tranfer from Excel which is where the user will
be working. Can I use this hear? I did some searching and this seems
to be a method for Access..or no? Thanks for your help.

-Scott
 
Try something like this. You may need to ensure that the workbook is
saved first:

Sub TestExportToAccess()
Dim oConn As Object 'ADODB.Connection
Dim SQL As String

Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=D:\MyDatabase.mdb;"

SQL = "INSERT INTO MyTable SELECT * FROM " _
& "[Excel 8.0;HDR=Yes;database=D:\MyWorkbook.xls;].[Sheet1$];"

oConn.Execute SQL

oConn.Close
End Sub
 
Thanks John for the response to my post. I tried the example you gave
me (changed to reflet my mdb path, etc.). Below is what I tried, and
which generated a run-time error:

Sub TestExportToAccess()
Dim oConn As Object 'ADODB.Connection
Dim SQL As String

dbpath = ThisWorkbook.Names("dbpath").RefersToRange 'this cell holds
the db path
dbpath2 = ThisWorkbook.Names("dbpath2").RefersToRange 'this cell holds
the excel file path
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbpath

SQL = "INSERT INTO Table1 SELECT * FROM [Excel 8.0;HDR=Yes;database=" &
dbpath2 & ";].[Sheet1$];"

oConn.Execute SQL
oConn.Close
End Sub

The error I get reads: "The INSERT INTO statement contains the
following unknown field: 'e'. Make sure you typed the name corretly
and try the operation again."

I have tired this using the actual path names in the code and received
the same result. Any idea what the problem might be? Thanks in
advance.

-Scott

Try something like this. You may need to ensure that the workbook is
saved first:

Sub TestExportToAccess()
Dim oConn As Object 'ADODB.Connection
Dim SQL As String

Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=D:\MyDatabase.mdb;"

SQL = "INSERT INTO MyTable SELECT * FROM " _
& "[Excel 8.0;HDR=Yes;database=D:\MyWorkbook.xls;].[Sheet1$];"

oConn.Execute SQL

oConn.Close
End Sub

I need to trigger the tranfer from Excel which is where the user will
be working. Can I use this hear? I did some searching and this seems
to be a method for Access..or no? Thanks for your help.
Click to expand...
-Scott
Click to expand...
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.- Hide quoted text -- Show quoted text -
Click to expand...
 
Back
Top