Pass Through SQL to Update SQL field

[EddiRae]

Hello,
I have an issue that I need help with.

I have an SQL table that uses bigint on the primary key field. When I lilnk this table into Access 2010, this table is not even visible because bigint isn't recognized by Access.

I have a field on this SQL table that I need to update and I was looking at using a Pass Through query in Access. I have a query in Access that creates a list of records that I need to update.

How would you suggest going about doing this?
What would be the Pass Through query?

Thanks for your help in advance.

Eddi Rae

 

[ButtonMoon]

When you say "SQL" do you mean "SQL Server"? Can the query you mention be passed-through (i.e. does it use tables that reside in SQL Server)?

Assuming the answer to both is yes, the form of the update might be:

UPDATE tbl
SET col = ... something you didn't specify
WHERE EXISTS
(... your query ...
WHERE x = tbl.keycol);

 

[EddiRae]

Yes, the SQL I mentioned is SQL Server. The values that I will be updating are in a table that is in Access.

I tried creating a pass-through query using that table in Access to update the SQL Server, but it stated that it wasn't in the SQL Server database.

Here is what I am wanting to do:

tbMemberInfo is the table on SQL Server
tblLEP_CMS_To_KCA is the table in Access

UPDATE tbMemberInfo
SET tbMemberInfo.PartDLateEnrollmentPenaltyAmount = tblLEP_CMS_To_KCA.CMS_Charge

FROM tbMemberInfo
INNER JOIN tblLEP_CMS_To_KCA
ON tbMemberInfo.MemCodNum = tblLEP_CMS_To_KCA.MemCodNum
WHERE tblLEP_CMS_To_KCA.UpdateMember =-1

I am getting the error message of Invalid Object Name 'tblLEP_CMS_To_KCA'

Thanks for your help in advance!!
Eddi Rae

 

[EddiRae]

I found that what I am trying to do wouldn't be feasible. I have created VBA code that goes through each record that is in Access and then uses ADO to update the record on SQL Server.

Thanks for everyone's help!!
Eddi Rae