1

Note that both databases are MS SQL Server.

The SELECT works fine and the code doesn't break until it gets to ADODB.Recordset.Update. The SQL account has all of the necessary permissions. The table [NASMSPAINT].[Ignition].[dbo].[booth_Styles] is a linked server. The User account I am using has enough permissions because I am able to UPDATE the table using Python. This is on a secure isolated network so security is of very little concern, this just needs to work using VB6 with ADO. Long story short, this code is part of a large application still using VB6 and rewriting the code in Visual Studio is not an option.

Using ADODB.Recordset.OPEN using adLockOptimistic option, the following error occurs on the ".Update" line of the code:

SQL server error message 16964 - for the optimistic cursor, timestamp columns are required if the update or delete targets are remote.

Using ADODB.Recordset.OPEN using adLockPessimistic option, the following error occurs on the ".Update" line of the code:

SQL Server Error Msg 16963 – You cannot specify scroll locking on a cursor that contains a remote table.

I have found very little information on the internet concerning these errors. I have set the following server option properties on the linked server on the database:

Collation Compatible: TRUE
Data Access:TRUE
RPC:TRUE
RPC Out:TRUE
Use Remote Collation:FALSE
Collation Name:
Connection Timeout:0
Query Timeout:0
Distributor:FALSE
Publisher:FALSE
Subscriber:FALSE
Lazy Schema Validation:FALSE
Enable Promotion of Distributed Transaction:TRUE

VB6 code:

sDBName = "PROVIDER=SQLOLEDB.1;Data Source=192.168.2.70;User ID=xxxx;Password=xxxx;Persist Security Info=False"

Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set Conn = New ADODB.Connection
Conn.Open sDBName
Set rs = New ADODB.Recordset

With rs
    .Open "SELECT * FROM [NASMSPAINT].[Ignition].[dbo].[booth_Styles] WHERE [Booth] = 'AdPro' ORDER BY  [StyleID]", Conn, adOpenDynamic, adLockOptimistic
    .MoveFirst
    nThisStyle = 1
    Do Until .EOF
        ![Plant_Number] = Style_Data(nThisStyle).PlantStyle
        ![Style_Number] = Style_Data(nThisStyle).FanucStyle
        ![Descript] = Style_Data(nThisStyle).StyleDesc
        ![Robots_Required] = Style_Data(nThisStyle).StyleRobotsReq
        .Update
        .MoveNext
        nThisStyle = nThisStyle + 1
    Loop
End With

The code breaks on the .Update line.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Kevin
  • 11
  • 2
  • 1
    I would write this an a `UPDATE` SQL statement from the beginning. Performance would be **greatly** improved, because you'd never need to transfer the data over the network. Right now data is first transferred in bulk to the base server and then again from there to your machine. Then data is transferred again as a separate operation for each record (not in bulk) first to your main server and finally a fourth time from there to linked server. Writing this as an UPDATE can remove **ALL** of that. The linked database can make all the changes internally. – Joel Coehoorn Jan 21 '23 at 19:45
  • 2
    Why use a linked server? Is there some reason you cannot connect directly to the `NASMSPAINT` server? – Dan Guzman Jan 21 '23 at 20:38
  • UPDATE , sp and execute directly sp on NASMSPAINT – Bogdan Sahlean Jan 21 '23 at 21:23
  • Have you tried? – Bogdan Sahlean Jan 21 '23 at 23:37
  • 2
    _Long story short, this code is part of a large application still using VB6 and rewriting the code in Visual Studio is not an option._ The VB6 IDE hasn't been supported since 2008 and the VB6 runtime does not receive feature updates, only security fixes and "just enough" work to keep it running in versions of Windows that still support 32 bit processes. At some point you have to give up chisels and stone tablets. Microsoft will force the issue for you when they finally go 64 bit-only. – AlwaysLearning Jan 22 '23 at 01:45
  • @AlwaysLearning you're right technically but most VB6 programmers don't have the sole discretion for that. – StayOnTarget Jan 22 '23 at 12:30
  • I haven't found any way to update remote tables with cursors even inside SQL Server. Any combinations either return same error about timestamp-columns, or some other error. I'm not sure how you manage to update from python, but i'm guessing you're not doing a cursor update, but a regular UPDATE t SET value = ... Looks like you have to do the same in vb6 – siggemannen Jan 22 '23 at 13:38
  • Or connect directly to the linked server without passing go – siggemannen Jan 23 '23 at 07:34

0 Answers0