Export MS Access table via OBDC using VB

[rjgst3]

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.Connectio n
Dim strsql as String

Set rs = CurrentDb.OpenR ecordset("selec t field1, field2, field3,
field4, field5.....from daotable").Open recordset
Move.First

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

conn.Connection String = "dsn=Databa se ; uid=UID; pwd=password"
conn.Connection Timeout = 0
conn.CommandTim eout = 0
conn.Open

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

conn.close

[Terry Kreft]

Re: Export MS Access table via OBDC using VB

It goes something like

Dim rs as DAO.Recordset
Dim conn as New ADODB.Connectio n
Dim strsql as String

Set rs = CurrentDb.OpenR ecordset("selec t field1, field2, field3,
field4, field5.....from daotable")

conn.Connection String = "dsn=Databa se ; uid=UID; pwd=password"
conn.Connection Timeout = 0
conn.CommandTim eout = 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" <rjgst3@rmu.edu > wrote in message
news:1134833779 .932172.287130@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> 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.Connectio n
> Dim strsql as String
>
> Set rs = CurrentDb.OpenR ecordset("selec t field1, field2, field3,
> field4, field5.....from daotable").Open recordset
> Move.First
>
> strsql = "insert into odbctable rs.value(field1 , field2, field3,
> field4, field5.....)
>
> conn.Connection String = "dsn=Databa se ; uid=UID; pwd=password"
> conn.Connection Timeout = 0
> conn.CommandTim eout = 0
> conn.Open
>
> Do
> conn.Execute (strsql)
> rs.MoveNext
> Loop Unit rs.EOF
>
> conn.close
>[/color]

[Bri]

Re: Export MS Access table via OBDC using VB

rjgst3 wrote:[color=blue]
> 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.Connectio n
> Dim strsql as String
>
> Set rs = CurrentDb.OpenR ecordset("selec t field1, field2, field3,
> field4, field5.....from daotable").Open recordset
> Move.First
>
> strsql = "insert into odbctable rs.value(field1 , field2, field3,
> field4, field5.....)
>
> conn.Connection String = "dsn=Databa se ; uid=UID; pwd=password"
> conn.Connection Timeout = 0
> conn.CommandTim eout = 0
> conn.Open
>
> Do
> conn.Execute (strsql)
> rs.MoveNext
> Loop Unit rs.EOF
>
> conn.close
>[/color]

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

Dim rs as DAO.Recordset
Dim conn as New ADODB.Connectio n
Dim strsql as String

Set rs = CurrentDb.OpenR ecordset("selec t field1, field2, field3,
field4, field5.....from daotable").Open recordset
Move.First

conn.Connection String = "dsn=Databa se ; uid=UID; pwd=password"
conn.Connection Timeout = 0
conn.CommandTim eout = 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