Excel Export to Access via VBA
 

Hi all.
Hopefully someone can point me in the right direction. Here's what I want to
do.
I want to be able to click on a command button and export whatever logs that
have been entered into the spreadsheet to be exported into an Access db with
the same field names as the spreadsheet. I've read several postings on here
but can't seem to get the coding down. I keep getting an error ont he first
line "user-defined type not defined". Below is the code I got from a
different posting here.
Any help is appreciated. Thanks.

Dim con As New adodb.Connection
Dim strcon As String
Dim strSQL As String

strcon = "Provider=Microsoft.Jet.OLEDB.4.0;"
strcon = strcon & "Data Source=U:\MY_CB_LOG1.mdb"
strcon = strcon & Persist Security Info=False"

con.Open strcon

strSQL = "INSERT INTO tbl_MAIN_CB_LOG "
strSQL = strSQL & "SELECT * FROM [Excel 8.0;Database=U:\MY_CB_LOG.xls].
[CB_DATA$]"

con.Execute strSQL

Set con = Nothing

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/201004/1

Excel Export to Access via VBA
 

On Apr 28, 9:35*pm, "AccessUser777 via OfficeKB.com" <u44103@uwe
wrote:
Hi all.
Hopefully someone can point me in the right direction. *Here's what I want to
do.
I want to be able to click on a command button and export whatever logs that
have been entered into the spreadsheet to be exported into an Access db with
the same field names as the spreadsheet. *I've read several postings on here
but can't seem to get the coding down. *I keep getting an error ont he first
line "user-defined type not defined". *Below is the code I got from a
different posting here.
Any help is appreciated. *Thanks.

* *Dim con As New adodb.Connection
* * Dim strcon As String
* * Dim strSQL As String

* * strcon = "Provider=Microsoft.Jet.OLEDB.4.0;"
* * strcon = strcon & "Data Source=U:\MY_CB_LOG1.mdb"
* * strcon = strcon & *Persist Security Info=False"

* * con.Open strcon

* * strSQL = "INSERT INTO tbl_MAIN_CB_LOG *"
* * strSQL = strSQL & "SELECT * *FROM [Excel 8.0;Database=U:\MY_CB_LOG.xls].
[CB_DATA$]"

* * con.Execute strSQL

* * Set con = Nothing

--
Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/201004/1

In VBA editor, go to Tools-References and check Microsoft ActiveX
Data Objects 2.* Library. This will add reference to ActiveX Data
objects and adodb.connection will then be defined.

Excel Export to Access via VBA
 

You are using the ADO method (ActiveX Data Object). You need to add two
references to your VBA from menu tools - Reference

1) Microsoft Access 120.0 Library Object (or latest on your PC)
2) Microsoft ActiveX Data Object 2.8 library (or latest on your PC)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=199279

http://www.thecodecage.com/forumz