Update external table with more than 255 columns?

[Tony]

Hi All,

I'm in desperate need of help. We've got an Access 2000 database that links
in some Progress database tables, one of which has more than 255 columns. I
need to run an update query in a column that is somewhere beyond the 255th
one. I have no problem running a select query to see the existing data
using a pass-through query, but I can't update using pass-through and am
unsure of how to accomplish this (if can even be done with Access). I've
also tried running SQL from a module to try to get around it, but it's not
recognizing the column name I'm trying to update, acting like it's expecting
an input value (getting the Enter Parameter dialog box). If anyone can
help, I would really appreciate it.

The SQL in the pass-through to see what's there looks like this:

//////////////
SELECT job_num AS "NUMBER", ctr AS "CTR"
FROM PUB."jc_job"
//////////////


The SQL I'm trying to run in the module looks like this:

//////////////
Dim sqlUpdate As String

sqlUpdate = "UPDATE PUB_jc_job SET PUB_jc_job.ctr = '" & "10" & "' " _
& "WHERE PUB_jc_job.job_num = '" & "8525" & "'"

DoCmd.RunSQL sqlUpdate
//////////////


'ctr' is the field I'm trying to get at with no success.

Thanks in advance & please let me know if this would be more appropriately
posted to the modules coding group. Didn't want to get flamed for cross
posting.

Tony

 

[Duane Hookom]

Can you create and then link to a view with only the required fields? Or,
create a pass-through query that runs your SQL statement.
Dim sqlUpdate As String

sqlUpdate = "UPDATE PUB_jc_job SET PUB_jc_job.ctr = '" & "10" & "' " _
& "WHERE PUB_jc_job.job_num = '" & "8525" & "'"
Currentdb.QueryDefs("qsptMyPT").SQL = sqlUpdate

then use code to open the query.

 

[Tony]

Hi Duane,

Thanks for the reply. I'll have to look at both these options. I've never
created a view but imagine it would be useful as this probably won't be the
last time I need to get at and manipulate columns beyond the 255th.

As always, I appreciate the help.

Tony