By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,724 Members | 859 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,724 IT Pros & Developers. It's quick & easy.

Export MS Access table via OBDC using VB

P: n/a
I'm trying to export a reference table from MS Access to a table via
ODBC. Is there a way I can use a DAO.Recordset and use the
value(field) to populate my ODBC table?

Dim rs as DAO.Recordset
Dim conn as New ADODB.Connection
Dim strsql as String

Set rs = CurrentDb.OpenRecordset("select field1, field2, field3,
field4, field5.....from daotable").Openrecordset
Move.First

strsql = "insert into odbctable rs.value(field1, field2, field3,
field4, field5.....)

conn.ConnectionString = "dsn=Database ; uid=UID; pwd=password"
conn.ConnectionTimeout = 0
conn.CommandTimeout = 0
conn.Open

Do
conn.Execute (strsql)
rs.MoveNext
Loop Unit rs.EOF

conn.close

Dec 17 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
It goes something like

Dim rs as DAO.Recordset
Dim conn as New ADODB.Connection
Dim strsql as String

Set rs = CurrentDb.OpenRecordset("select field1, field2, field3,
field4, field5.....from daotable")

conn.ConnectionString = "dsn=Database ; uid=UID; pwd=password"
conn.ConnectionTimeout = 0
conn.CommandTimeout = 0
conn.Open

with rs
do until .eof
strsql = "insert into odbctable ( F1, F2, F4, F4, F5) " _
& "VALUES (" & .Fields("field1") & ", " .Fields("field2") _
& ", " & .Fields("field3") & ", " & .Fields("field4" _
& ", " & .Fields("field5") & ")"

conn.execute strsql
.movenext
loop
end with
conn.close

' etc...

You'll need t insert single quotes (') in their as well if the fields are
strings.

--
Terry Kreft

"rjgst3" <rj****@rmu.edu> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I'm trying to export a reference table from MS Access to a table via
ODBC. Is there a way I can use a DAO.Recordset and use the
value(field) to populate my ODBC table?

Dim rs as DAO.Recordset
Dim conn as New ADODB.Connection
Dim strsql as String

Set rs = CurrentDb.OpenRecordset("select field1, field2, field3,
field4, field5.....from daotable").Openrecordset
Move.First

strsql = "insert into odbctable rs.value(field1, field2, field3,
field4, field5.....)

conn.ConnectionString = "dsn=Database ; uid=UID; pwd=password"
conn.ConnectionTimeout = 0
conn.CommandTimeout = 0
conn.Open

Do
conn.Execute (strsql)
rs.MoveNext
Loop Unit rs.EOF

conn.close

Dec 17 '05 #2

P: n/a
Bri
rjgst3 wrote:
I'm trying to export a reference table from MS Access to a table via
ODBC. Is there a way I can use a DAO.Recordset and use the
value(field) to populate my ODBC table?

Dim rs as DAO.Recordset
Dim conn as New ADODB.Connection
Dim strsql as String

Set rs = CurrentDb.OpenRecordset("select field1, field2, field3,
field4, field5.....from daotable").Openrecordset
Move.First

strsql = "insert into odbctable rs.value(field1, field2, field3,
field4, field5.....)

conn.ConnectionString = "dsn=Database ; uid=UID; pwd=password"
conn.ConnectionTimeout = 0
conn.CommandTimeout = 0
conn.Open

Do
conn.Execute (strsql)
rs.MoveNext
Loop Unit rs.EOF

conn.close


AIRCODE based on your sample code
=======

Dim rs as DAO.Recordset
Dim conn as New ADODB.Connection
Dim strsql as String

Set rs = CurrentDb.OpenRecordset("select field1, field2, field3,
field4, field5.....from daotable").Openrecordset
Move.First

conn.ConnectionString = "dsn=Database ; uid=UID; pwd=password"
conn.ConnectionTimeout = 0
conn.CommandTimeout = 0
conn.Open

Do
INSERT INTO Member ( MemberID, [CCA-ID] )
SELECT Member.MemberID, Member.[CCA-ID]
FROM Member;

strsql = "insert into odbctable (field1, field2, field3)" & _
"Values (" & rs("field1") & ", " & rs("field2") & _
", " & rs("field3") & ") "

conn.Execute (strsql)
rs.MoveNext
Loop Unit rs.EOF

conn.close

END AIRCODE

--
Bri

Dec 17 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.