Export information to Access.
 

Trying to automate an import process.
Could some one share the code to export all the data from a specific Excel
sheet to an existing Access database table?

Thanks!
jvs

Export information to Access.
 

Does not record as part of auto record macro. I put in
for help "access" and came up with this to begin with,
which indicates a need to open the access file in a
specific mode to write to it. I think you could do an
append from the clipboard, but I don't know access very
well. Maybe this will get you started in the right
direction.

Open Statement
Enables input/output (I/O) to a file.
Syntax
Open pathname For mode [Access access] [lock] As [#]
filenumber [Len=reclength]
The Open statement syntax has these parts:


-----Original Message-----
Trying to automate an import process.
Could some one share the code to export all the data
from a specific Excel
sheet to an existing Access database table?

Thanks!
jvs


.

Export information to Access.
 

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"JVS" wrote in message
...
Trying to automate an import process.
Could some one share the code to export all the data from a specific Excel
sheet to an existing Access database table?

Thanks!
jvs

Export information to Access.
 

Su

Sub tester()

Dim oConn As Object
Dim lngRowsAffected As Long
Set oConn = CreateObject("ADODB.Connection")

With oConn
.ConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source=C:\Tempo\New_Jet_DB.mdb"
.Open
.Execute "INSERT INTO MyTable" & _
" SELECT MyCol1 AS RefID, " & _
" MyCol2 AS Surname" & _
" FROM [Excel 8.0;Database=C:\Tempo\db.xls].[Sheet1$]", _
lngRowsAffected
.Close
End With

End Sub

--

"JVS" wrote in message ...
Trying to automate an import process.
Could some one share the code to export all the data from a specific Excel
sheet to an existing Access database table?

Thanks!
jvs