Copying from one db into another

[John]

Hi

I am trying to copy data from one db into another. Thanks for everyone who
suggested ways to do that. After some more research I have come up with the
below simple looking way. My question in, is it supposed to work this way
and will it work?

LocalConn.Open()

Dim DBCommand As New System.Data.OleDb.OleDbCommand("SELECT id, name, date
INTO (localtable) FROM remotetable IN [Data Source=<ip address>;Initial
Catalog=&quot;RemoteDB&quot;;Persist Security Info=True;User
ID=&quot;username&quot;;Password=password];", LocalConn)

DBCommand.ExecuteNonQuery()

LocalConn.Close()

The remote db is a sql server.

Many Thanks

Regards

 

[Stephany Young]

What happened when you tried it?

 

[John]

Message="The SELECT statement includes a reserved word or an argument name
that is misspelled or missing, or the punctuation is incorrect."

Source="Microsoft JET Database Engine"

I am new to this syntax (when two dbs are involved) so not sure which
parameter is invalid. LocalConn is to a local access db.

Any help would be appreciated.

Thanks

Regards

What happened when you tried it?

Hi

I am trying to copy data from one db into another. Thanks for everyone
who suggested ways to do that. After some more research I have come up
with the below simple looking way. My question in, is it supposed to work
this way and will it work?

LocalConn.Open()

Dim DBCommand As New System.Data.OleDb.OleDbCommand("SELECT id, name,
date INTO (localtable) FROM remotetable IN [Data Source=<ip
address>;Initial Catalog=&quot;RemoteDB&quot;;Persist Security
Info=True;User ID=&quot;username&quot;;Password=password];", LocalConn)

DBCommand.ExecuteNonQuery()

LocalConn.Close()

The remote db is a sql server.

Many Thanks

Regards

 

[Stephany Young]

Try Select id, name, [date] ...

Date is a reserved word in Jet and to use it as a column name in a SQL
statement you have to decorate it.

Message="The SELECT statement includes a reserved word or an argument name
that is misspelled or missing, or the punctuation is incorrect."

Source="Microsoft JET Database Engine"

I am new to this syntax (when two dbs are involved) so not sure which
parameter is invalid. LocalConn is to a local access db.

Any help would be appreciated.

Thanks

Regards

What happened when you tried it?

Hi

I am trying to copy data from one db into another. Thanks for everyone
who suggested ways to do that. After some more research I have come up
with the below simple looking way. My question in, is it supposed to
work this way and will it work?

LocalConn.Open()

Dim DBCommand As New System.Data.OleDb.OleDbCommand("SELECT id, name,
date INTO (localtable) FROM remotetable IN [Data Source=<ip
address>;Initial Catalog=&quot;RemoteDB&quot;;Persist Security
Info=True;User ID=&quot;username&quot;;Password=password];", LocalConn)

DBCommand.ExecuteNonQuery()

LocalConn.Close()

The remote db is a sql server.

Many Thanks

Regards

 

[John]

Thanks. Still says the same things. ErrorCode=-2147217900 is also the same.

Regards

Try Select id, name, [date] ...

Date is a reserved word in Jet and to use it as a column name in a SQL
statement you have to decorate it.

Message="The SELECT statement includes a reserved word or an argument
name that is misspelled or missing, or the punctuation is incorrect."

Source="Microsoft JET Database Engine"

I am new to this syntax (when two dbs are involved) so not sure which
parameter is invalid. LocalConn is to a local access db.

Any help would be appreciated.

Thanks

Regards

What happened when you tried it?


Hi

I am trying to copy data from one db into another. Thanks for everyone
who suggested ways to do that. After some more research I have come up
with the below simple looking way. My question in, is it supposed to
work this way and will it work?

LocalConn.Open()

Dim DBCommand As New System.Data.OleDb.OleDbCommand("SELECT id, name,
date INTO (localtable) FROM remotetable IN [Data Source=<ip
address>;Initial Catalog=&quot;RemoteDB&quot;;Persist Security
Info=True;User ID=&quot;username&quot;;Password=password];", LocalConn)

DBCommand.ExecuteNonQuery()

LocalConn.Close()

The remote db is a sql server.

Many Thanks

Regards

 

[Paul Clement]

¤ Thanks. Still says the same things. ErrorCode=-2147217900 is also the same.
¤

Both Name and Date are reserved words.


Paul
~~~~
Microsoft MVP (Visual Basic)