Import Excel data to Access table

[ryu]

I am writing the VBA code in Excel to copy the Excel data to a table i
Access database. I am using the function DoCmd.TransferSpreadsheet to d
the job but I keep getting the "Compiled Error: Variable not Defined
error message whenever I run the code. I am not sure what I am doin
wrong. Please help!!

Here is my code.

Sub CopyToAccessTable()
Dim oApp As Object
Dim LPath As String

LPath = "C:\Work\test.mdb"
Set oApp = CreateObject("Access.Application")
oApp.OpenCurrentDatabase LPath
oApp.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9
"Additional_Accounts", "C:\Work\test.xls", True

End Sub


Thank you so much for your hel

 

[Jim Thomlinson]

ADODB is a better way to write data into Access

http://www.erlandsendata.no/english/index.php?d=envbadacexportado

 

[ryu]

Thank you so much Jim for the reply. I tried the code you sent me but
am getting the following error:

"Compile Error: User-defined type not defined" on
Dim cn As ADODB.Connection

Any help is appreciated.

Thanks,
Jill

 

[arjen van...]

You shoudl just need to add a refernce to the ADO library in your project.
InVisual Basic Editor, go to Tools > References and look for "Microsoft
ActiveX Data Objects 2.8 Library". Put a check beside it and click OK. Note
2.8 is for Excel 2003, the number you see may vary accordingly.

 

[ryguy7272]

Follow up to Jim and arjen's posts:
http://www.cpearson.com/excel/References.htm

Also, you may want to take a look at this:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

HTH,
Ryan---

 

[ryu]

Thanks everyone for your help. I finally got it to work and I am able t
copy the data from Excel to Access table. However, it seems like the ol
data in the Access table doesn't get wiped out when copying new sets o
data from Excel to the same Access table. Is there anyway that I can ge
rid of the old data in the Access table before copying the new set o
data from Excel to Access?

Thanks,
Jill

 

[arjen van...]

If you need to get rid of all the old data first, you'll have to run a delete
query. If running it from Excel using ADO you can create a string variable:

Dim strDelete As String
strDelete = "DELETE * " & _
"FROM tableName"

Here's an example using the Northwind database:

Sub Cleartable()

Dim strConnection As String
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source = C:\VB
2008\Northwind_copy.accdb"
Dim strDelete As String
strDelete = "DELETE * " & _
"FROM Invoices"

Dim objCmd As New ADODB.Command

Dim objConn As New ADODB.Connection
objConn.Open strConnection


objCmd.ActiveConnection = objConn

objCmd.CommandText = strDelete
objCmd.CommandType = adCmdText
objCmd.Execute

Set objCmd = Nothing
Set objConn = Nothing

End Sub

Note:this assumes you're using Access 2007, if not :
strConnection = "Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source = C:\VB 2008\Northwind_copy.mdb"

 

[ryguy7272]

DoCmd.SetWarnings False

DoCmd.RunSQL ("DELETE * FROM [Table1];")
'Your import code here

DoCmd.SetWarnings True



HTH,
Ryan--

 

[ryu]

One more question: Using ADODB, can I append new fields or new data t
the table in Access? If so, how?

Thank you so much,
Jill

 

[ryu]

Hey Arjan,

Is there any way that we can add "Microsoft ActiveX Data Objects 2.
Library" in the VBA code? The reason I am asking is because we ar
giving the code to our client and we don't want them to have to go int
the VBA to turn it on themselves.

Thanks.