Updating SQL 2000 server using VB6

[DesDj]

Hi,

I developed an application using VB6 and Access. I now want to move from Access to SQL2000 server. My problem is this, I am connecting to the Database using system DSN and I am getting a system timeout when I try to update a table using code.

example of code is:

Qry = "UPDATE Contract INNER JOIN ContractBenefit ON Contract.ContractId = ContractBenefit.Contractid "
Qry = Qry & "SET ContractBenefit.Startdate = '" & GetDateStr(rspol!DateOfentry) & "', "
Qry = Qry & " ContractBenefit.Enddate = '" & GetDateStr(DateSerial(Year(rspol!DateOfentry), Month(rspol!DateOfentry) + 12, 1 - 1)) & "', "
Qry = Qry & " ContractBenefit.LastUpdateProg = '" & "Nyasys - Issue" & "', ContractBenefit.LastUpdateTime = Now(), "
Qry = Qry & " ContractBenefit.LastUpdateUser = '" & sUserName & "'"
Qry = Qry & " WHERE (((Contract.ContractNumber)='" & txtContract & "'));"
cnCon.Execute Qry

I have to build the query, passing values, then execute. the variable QRY contains the sql statement.

Please help

Desdj

[dorinbogdan]

Update the connection string to use OLEDB or SQL Server, instead of DSN.

Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
or 
Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;