Copying from one db into another

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
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

Stephany Young said:
What happened when you tried it?


John said:
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
Click to expand...
Click to expand...
 
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.


John said:
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

Stephany Young said:
What happened when you tried it?


John said:
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
Click to expand...
Click to expand...
Click to expand...
 
Thanks. Still says the same things. ErrorCode=-2147217900 is also the same.

Regards

Stephany Young said:
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.


John said:
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

Stephany Young said:
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
Click to expand...
Click to expand...
Click to expand...
 
¤ Thanks. Still says the same things. ErrorCode=-2147217900 is also the same.
¤

Both Name and Date are reserved words.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top