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

appending local table to remote table using pass-through query

P: n/a
I'm using Access 2000 and I'm trying to create a pass-through query to
append all the data from a local table to a remote table.

I'm using the SQL query editor in MS Access to create the query (I
don't want to do this in VB code).

I've set up the ODBC connection string in the query properties, which
has been tested and works:
ODBC;DSN=dev001 student99;Description=dev001
student99;UID=myusername;PWD=mypassword;DATABASE=s tudent99;Network=DBMSSPXN

I want to append all the records from a table in my local Access
database called upload_test to the remote table called cst_record.
Both tables have the same design.

Here is the SQL I'm trying to use:
INSERT INTO cst_record
SELECT *
FROM upload_test;

This returns the error "Invalid object name 'upload_test'."
Obviously, it's trying to find the table upload_test on the remote
database and fails.
What's the syntax I should use to get it to look to the local Access
db for the upload_test table?
The local Access database filename is C:\TEST\UPLOAD_CST.MDB
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
have you tried this?

INSERT INTO cst_record
SELECT *
FROM upload_test IN 'C:\TEST\UPLOAD_CST.MDB';

"Access Newbie" <ac****************@yahoo.com> wrote in message
news:51**************************@posting.google.c om...
I'm using Access 2000 and I'm trying to create a pass-through query to
append all the data from a local table to a remote table.

I'm using the SQL query editor in MS Access to create the query (I
don't want to do this in VB code).

I've set up the ODBC connection string in the query properties, which
has been tested and works:
ODBC;DSN=dev001 student99;Description=dev001
student99;UID=myusername;PWD=mypassword;DATABASE=s tudent99;Network=DBMSSPXN
I want to append all the records from a table in my local Access
database called upload_test to the remote table called cst_record.
Both tables have the same design.

Here is the SQL I'm trying to use:
INSERT INTO cst_record
SELECT *
FROM upload_test;

This returns the error "Invalid object name 'upload_test'."
Obviously, it's trying to find the table upload_test on the remote
database and fails.
What's the syntax I should use to get it to look to the local Access
db for the upload_test table?
The local Access database filename is C:\TEST\UPLOAD_CST.MDB

Nov 13 '05 #2

P: n/a
When I try that it returns the following error message:
ODBC--call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near
the keyword 'IN'.(#156)

"Marvin Henry" <ma*********@earthlink.net> wrote in message news:<sN*****************@newsread2.news.atl.earth link.net>...
have you tried this?

INSERT INTO cst_record
SELECT *
FROM upload_test IN 'C:\TEST\UPLOAD_CST.MDB';

"Access Newbie" <ac****************@yahoo.com> wrote in message
news:51**************************@posting.google.c om...
I'm using Access 2000 and I'm trying to create a pass-through query to
append all the data from a local table to a remote table.

I'm using the SQL query editor in MS Access to create the query (I
don't want to do this in VB code).

I've set up the ODBC connection string in the query properties, which
has been tested and works:
ODBC;DSN=dev001 student99;Description=dev001

student99;UID=myusername;PWD=mypassword;DATABASE=s tudent99;Network=DBMSSPXN

I want to append all the records from a table in my local Access
database called upload_test to the remote table called cst_record.
Both tables have the same design.

Here is the SQL I'm trying to use:
INSERT INTO cst_record
SELECT *
FROM upload_test;

This returns the error "Invalid object name 'upload_test'."
Obviously, it's trying to find the table upload_test on the remote
database and fails.
What's the syntax I should use to get it to look to the local Access
db for the upload_test table?
The local Access database filename is C:\TEST\UPLOAD_CST.MDB

Nov 13 '05 #3

P: n/a
why not create a link to your SQL Server table in your Access DB and
then just create an append query?
Nov 13 '05 #4

P: n/a
pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...
why not create a link to your SQL Server table in your Access DB and
then just create an append query?


Because I have 1.5 million records to append to the SQL Server table
and doing it that way takes too long. I need to figure out how to do
it using a pass-through query.

BTW, I figured out how to do it using BCP on the command line, but
that involves an extra step of exporting the Access table to a text
file. I'd like to skip that step and use an Access pass-through query
to append to the SQL Server table directly, but I can't figure out how
to reference the local Access table in the SQL statement. Any ideas?
Nov 13 '05 #5

P: n/a
"Access Newbie" <ac****************@yahoo.com> wrote in message
news:51**************************@posting.google.c om...
pi********@hotmail.com (Pieter Linden) wrote in message

news:<bf**************************@posting.google. com>...
why not create a link to your SQL Server table in your Access DB and
then just create an append query?


Because I have 1.5 million records to append to the SQL Server table
and doing it that way takes too long. I need to figure out how to do
it using a pass-through query.

BTW, I figured out how to do it using BCP on the command line, but
that involves an extra step of exporting the Access table to a text
file. I'd like to skip that step and use an Access pass-through query
to append to the SQL Server table directly, but I can't figure out how
to reference the local Access table in the SQL statement. Any ideas?


PMFJI.

A pass-through query CAN'T reference a local Access table. Pass-through
queries run on the server: they don't know anything about the MDB that
they're located in.

Can you possible use DTS?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
Nov 13 '05 #6

P: n/a
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message news:<Wr*****************@news04.bloor.is.net.cabl e.rogers.com>...
"Access Newbie" <ac****************@yahoo.com> wrote in message
news:51**************************@posting.google.c om...
pi********@hotmail.com (Pieter Linden) wrote in message

news:<bf**************************@posting.google. com>...
why not create a link to your SQL Server table in your Access DB and
then just create an append query?


Because I have 1.5 million records to append to the SQL Server table
and doing it that way takes too long. I need to figure out how to do
it using a pass-through query.

BTW, I figured out how to do it using BCP on the command line, but
that involves an extra step of exporting the Access table to a text
file. I'd like to skip that step and use an Access pass-through query
to append to the SQL Server table directly, but I can't figure out how
to reference the local Access table in the SQL statement. Any ideas?


PMFJI.

A pass-through query CAN'T reference a local Access table. Pass-through
queries run on the server: they don't know anything about the MDB that
they're located in.

Can you possible use DTS?


Thanks for the info. Since I was ultimately able to do it using BCP
fairly easily, I'll just keep doing it that way.
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.