appending local table to remote table using pass-through query

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

re: appending local table to remote table using pass-through query

have you tried this?

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





"Access Newbie" <access_2000_newbie@yahoo.com> wrote in message
news:51928137.0408241215.7200b2e0@posting.google.c om...[color=blue]
> 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
>[/color]
student99;UID=myusername;PWD=mypassword;DATABASE=s tudent99;Network=DBMSSPXN[color=blue]
>
> 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[/color]

re: appending local table to remote table using pass-through query

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" <marvinhenry@earthlink.net> wrote in message news:<sNSWc.3126$Y%3.1775@newsread2.news.atl.earth link.net>...[color=blue]
> have you tried this?
>
> INSERT INTO cst_record
> SELECT *
> FROM upload_test IN 'C:\TEST\UPLOAD_CST.MDB';
>
>
>
>
>
> "Access Newbie" <access_2000_newbie@yahoo.com> wrote in message
> news:51928137.0408241215.7200b2e0@posting.google.c om...[color=green]
> > 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
> >[/color]
> student99;UID=myusername;PWD=mypassword;DATABASE=s tudent99;Network=DBMSSPXN[color=green]
> >
> > 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[/color][/color]

re: appending local table to remote table using pass-through query

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

re: appending local table to remote table using pass-through query

pietlinden@hotmail.com (Pieter Linden) wrote in message news:<bf31e41b.0408251456.5bb1aaa3@posting.google. com>...[color=blue]
> why not create a link to your SQL Server table in your Access DB and
> then just create an append query?[/color]

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?

re: appending local table to remote table using pass-through query

"Access Newbie" <access_2000_newbie@yahoo.com> wrote in message
news:51928137.0408260936.74ad0e85@posting.google.c om...[color=blue]
> pietlinden@hotmail.com (Pieter Linden) wrote in message[/color]
news:<bf31e41b.0408251456.5bb1aaa3@posting.google. com>...[color=blue][color=green]
> > why not create a link to your SQL Server table in your Access DB and
> > then just create an append query?[/color]
>
> 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?[/color]

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

re: appending local table to remote table using pass-through query

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message news:<WrpXc.1485$A1t.1025@news04.bloor.is.net.cabl e.rogers.com>...[color=blue]
> "Access Newbie" <access_2000_newbie@yahoo.com> wrote in message
> news:51928137.0408260936.74ad0e85@posting.google.c om...[color=green]
> > pietlinden@hotmail.com (Pieter Linden) wrote in message[/color]
> news:<bf31e41b.0408251456.5bb1aaa3@posting.google. com>...[color=green][color=darkred]
> > > why not create a link to your SQL Server table in your Access DB and
> > > then just create an append query?[/color]
> >
> > 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?[/color]
>
> 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?[/color]

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