appending local table to remote table using pass-through query

[Access Newbie]

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

[Marvin Henry]

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

[Access Newbie]

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

[Pieter Linden]

why not create a link to your SQL Server table in your Access DB and
then just create an append query?

[Access Newbie]

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?

[Douglas J. Steele]

"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!)

[Access Newbie]

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