Append query in Access to SQL Server

[David G]

Hi,
I have data in Access 2003 that I want to append to SQL Server 2000. I have
created a linked table and tested the connection, all is well. I want to
create an append query in Access to add data to the SQL table. In the Access
QBE design view I select the Append Query from the Query Type list. When I
click on the Append query button it does not have the linked SQL Server table
in the list of available tables. What is wrong? What can I do?

 

[Albert D. Kallal]

It should appear. Are you sure this is a append query, or a make table
query?

The "target" table can be any local table, and the target table can be any
linked table.

You can always type in the append query in the sql builder such as:


insert dbo_Patient
select LoctalTable.* from localtable


I often by accident create a query that is pass-through to a table on the
server side, and then realize that I knead a REAL linked table...so, check
that you linked table is in fact a linked table, not a query.
For the most part I don't see why your list of linked tables
don't appear when you do this. You can as mentioned type in
the sql by hand in the sql...

 

[David G]

Thanks Albert,

I did try re-linking to the SQL Server table. That tested OK. I then
created a new append query and presto! the table was there. So all is well.
I can't explain why the table wasn't in the append list to begin with but, I
guess that's computing.
Much appreciated for your response.