ADO Copy Table to new MDB Help!

[jpodesta]

Can ADO be used to insert data into a new table in a new MDB file?

I have tried several things and so far no luck.

Will something like the code below work?

Private Sub Command7_Click()

Dim loConnection As ADODB.Connection

Set loConnection = New ADODB.Connection
loConnection.Open "Provider='Microsoft.JET.OLEDB.4.0';" & _
"Data
Source='F:\DATABASES\NBIMIncome\NBIM_BE.mdb'"
lcSQL = "INSERT INTO t_NBIMFIprebal2 SELECT * FROM "
lcSQL = lcSQL &
"[F:\DATABASES\NBIMIncome\NBIM_BE.mdb].[t_NBIMFIprebal]"""


loConnection.Execute lcSQL, , adExecuteNoRecords
loConnection.Close
Set loConnection = Nothing

End Sub

[lylefair]

Your code is hard to understand and it contains many errors.
Regardless, I suspect that ADO is not the technology of choice for the
job you describe.

If you were to describe that job more carefully and clearly, I am
guessing that several posters here will have a ready answer.

If there is some requirement that ADO be used, (I cannot imagine what
that could be), please, note that too

[lylefair]

I apologise if this appears twice; there a was little problem here when
I thought I had posted before:
It seems ADO can do this (if I understand "this" properly):

Dim c As ADODB.Connection
Dim constr As String
Dim sql As String
Set c = New ADODB.Connection
constr = "PROVIDER=Microsoft.Jet.OLEDB.4.0"
constr = constr & ";DATA SOURCE=C:\Documents and Settings\Lyle
Fairfield\My Documents\Access\db2.mdb"
constr = constr & ";Jet OLEDB:System database=C:\Documents and
Settings\Lyle Fairfield\Application Data\Microsoft\Access\System.mdw"
sql = "INSERT INTO"
sql = sql & vbNewLine & "[C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\tests.mdb].[table1]"
sql = sql & vbNewLine & " SELECT * FROM"
sql = sql & vbNewLine & "[C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\copy of tests.mdb].[table1]"
With c
.Open constr
.Execute sql
End With

But of course, ADO is simply connecting to JET and asking JET to do the
task;

CurrentProject.Connection
CurrentProject.AccessConnection
and
DBEngine(0)(0) can each be used instead of the created ADO connection.

If I had a DAO reference, I would use DBEngine(0)(0) as it may be more
efficient in executing JET queries than ADO connections are.

[lylefair]

Well, upon a little testing it seems that ADO can do this (or it can do
what I think your question was):

Dim c As ADODB.Connection
Dim constr As String
Dim sql As String
Set c = New ADODB.Connection
constr = "PROVIDER=Microsoft.Jet.OLEDB.4.0"
constr = constr & ";DATA SOURCE=C:\Documents and Settings\Lyle
Fairfield\My Documents\Access\db2.mdb"
constr = constr & ";Jet OLEDB:System database=C:\Documents and
Settings\Lyle Fairfield\Application Data\Microsoft\Access\System.mdw"
sql = "INSERT INTO"
sql = sql & vbNewLine & "[C:\Documents and Settings\Lyle
Fairfield\My Documents\Access\tests.mdb].[table1]"
sql = sql & vbNewLine & " SELECT * FROM"
sql = sql & vbNewLine & "[C:\Documents and Settings\Lyle
Fairfield\My Documents\Access\copy of tests.mdb].[table1]"
With c
.Open constr
.Execute sql
End With

But of course, ADO is not "doing" anything. It is merely connecting to
JET and asking JET to do it.
DbEngine(0)(0) (assuming a DAO ref) would do it as well and probably
more effciently.
And, of course, CurrentProject.Connection would do it as would
CurrentProject.AccessConnection

[jpodesta]

thank you.

[David W. Fenton]

"lylefair" <ly***********@aim.com> wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:

If I had a DAO reference, I would use DBEngine(0)(0) as it may be
more efficient in executing JET queries than ADO connections are.

DBEngine is a member of the Access Application as well as DAO, and
because of that, you can execute SQL using it without needing a DAO
reference in your database at all.

I just tried it, and it works.

If all you're doing is executing SQL, then you don't need either DAO
or ADO references.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[lylefair]

David W. Fenton wrote:

DBEngine is a member of the Access Application as well as DAO, and
because of that, you can execute SQL using it without needing a DAO
reference in your database at all.

Thank you for pointing that out. I was unaware of it.

[David W. Fenton]

"lylefair" <ly***********@aim.com> wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:

David W. Fenton wrote:

DBEngine is a member of the Access Application as well as DAO,
and because of that, you can execute SQL using it without needing
a DAO reference in your database at all.

Thank you for pointing that out. I was unaware of it.

This also applies to CurrentDB().

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc