Update a Linked SQL table

[bubba100]

I am trying to figure how to update a linked SQL table with information in a local table. Until yesterday I had no experience with pass through queries so the learning curve is vertical. The following Access query shows the records that need to updated but I can't seem to be able to write the SQL pass through. Can you "show the way"?

SELECT DISTINCT iftadata.*
FROM OldIFTA INNER JOIN iftadata ON (OldIFTA.RETPERYM = iftadata.RETPERYM) AND (OldIFTA.FUTSNO = iftadata.FUTSNO);

I get an error message about the table Oldifta. I presume it not looking local to find it. Thanks in advance for your help.

 

[lameid]

First, I would not use and SQL Pass Through query for this(SPT query).

That said, in order to do this with a SPT query, you would have to open the access data in a Recordset and step through the data and build an SQL statement to update each record. In short this is obnoxious. Or you would have to somehow link to the access table in SQL Server and write your statement in SQL server.

The good news is that if you can link to an SQL table via ODBC, Access is smart enough to manage changes to it like it was a native access table. There maybe a few hiccups (it simply won't work) in some cases but by in large you should be able to treat any linked table in access like an access table. The limitations of the ODBC driver will limit what you can and can't do. One thing to note here is that Access does table scans. When you write a query that joins to a linked sql table and do all the logic in Access it reads all the data accross the network and process it locally. This is even slower than using an Access table. That is why SPT queries are stressed when getting data from SQL server. In this case, writing the code to do the update the long way will likely take longer than just letting Access cludge it.

Also noteworthy is that you could import the Access table to SQL server and then perform the update in SQL Server. Arguably there may be better solutions for SQL Server but the solution you understand is often the quickest way to get it done.

 

[bubba100]

I can add to and delete from the SQL table but can't update. I have tried creating a Access query but it tells me that the operation must be an updatable query or it just stops reponding at some point. I have about 180,000 records that will be updated out of 750,000.

 

[lameid]

What's your SQL statement?

Can you update a record in the linked table manually?

 

[bubba100]

Here is a select SQL that I am trying to make first. Learn one step then go to the next. When run I get an SQL error "invalid Object Oldifta name #208". I can update a record manually.

SELECT iftadata.*
FROM Oldifta INNER JOIN iftadata ON (Oldifta.FUTSNO = iftadata.FUTSNO) AND (Oldifta.RETPERYM = iftadata.RETPERYM);

Thanks.

 

[lameid]

Oldifta is the Access table?

Are the datatypes of the field you are joining on compatible (both number data types or both text data types)?

Can you select just from the SQL server table?

 

[bubba100]

Oldifta is the local table. The joins are the same type. Yes, I can select just from the the SQL with either an Access or SQL query. I bomb when I try an run an SQL referencing the local table or I try and use an Access query to update the SQL or an Access query to delete from the SQL.

 

[lameid]

And you ARE NOT using an SPT query? (You can hit a new query, paste in the above SQL and get the same message.)

 

[bubba100]

Not sure what is an SPT query. And yes about creating a new SQL. Tried copying the above and starting over and retyping. Time to leave until tomorrow. Thanks.

 

[lameid]

SPT (SQL Pass Through), see above.

According to my Google Search research it looks like the error is an SQL server error suggesting you are using either a SPT query or are in SQL server instead of running an Access / Jet query. The other possibility: your SQL 2000 server needs patches.

 

[lameid]

I'm sorry I didn't think it through... It has to be the fact you are sending the SQL statement directly to SQL server otherwise you would not get an error about your Access local table.