Sql2000 to SqlExpress
Hi
I have a Sql2000 database which I put onto a SqlExpress server. The way I did this was to take the PosServer.bak and restore it into the SqlExpress server.
My program which is access2000 had problem with the new database which I fixed by removing relationships from the database and creating them again.
Another program which looks at the same data is now having trouble saving data to the database although it connects and reads data no problem.
The following code:
Dim RsNewDlv As ADODB.Recordset
Set RsNewDlv = New ADODB.Recordset
RsNewDlv.Open "Select top 1 * From DeliveryHead", CnnPosServer, adOpenKeyset, adLockOptimistic, adCmdText
RsNewDlv.AddNew
RsNewDlv!DeliveryNo = gblDeliveryNo
RsNewDlv!GrnCreated = False
RsNewDlv.Update
Gives this error
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server 'QLIKVIEW\SQLEXPRESS' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
this is the connection string.
"Driver={SQL Server};Server=QLIKVIEW\SQ
What might be the cause of this. I don't understand why I should have to add it as a linked server because it is not and I tried it anyway and different errors came up.
I have a Sql2000 database which I put onto a SqlExpress server. The way I did this was to take the PosServer.bak and restore it into the SqlExpress server.
My program which is access2000 had problem with the new database which I fixed by removing relationships from the database and creating them again.
Another program which looks at the same data is now having trouble saving data to the database although it connects and reads data no problem.
The following code:
Dim RsNewDlv As ADODB.Recordset
Set RsNewDlv = New ADODB.Recordset
RsNewDlv.Open "Select top 1 * From DeliveryHead", CnnPosServer, adOpenKeyset, adLockOptimistic, adCmdText
RsNewDlv.AddNew
RsNewDlv!DeliveryNo = gblDeliveryNo
RsNewDlv!GrnCreated = False
RsNewDlv.Update
Gives this error
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server 'QLIKVIEW\SQLEXPRESS' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
this is the connection string.
"Driver={SQL Server};Server=QLIKVIEW\SQ
What might be the cause of this. I don't understand why I should have to add it as a linked server because it is not and I tried it anyway and different errors came up.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the ADO version should not make a difference - however, for compatibility, I usually use 2.6....
ASKER
Thanks gbshahaq using an OLE DB connection instead seems to work. I won't accept just yet until I have had a chance to fully check it.
Can you explain why my old connection string would not work.
Also if I replace my connection strings with the new one, in locations where I am still using sql2000 will I run into problems.
Can you explain why my old connection string would not work.
Also if I replace my connection strings with the new one, in locations where I am still using sql2000 will I run into problems.
no probs - glad you're starting to get there....
the trouble with ODBC that i think you have is it had established a "READ ONLY" connection - not quite sure why this happens though....
the OLE DB connection string will work with 2000 just fine - the only change you'll need to make is the DATA SOURCE parameter, assuming that everything else remains the same (database name, user and pass)
the trouble with ODBC that i think you have is it had established a "READ ONLY" connection - not quite sure why this happens though....
the OLE DB connection string will work with 2000 just fine - the only change you'll need to make is the DATA SOURCE parameter, assuming that everything else remains the same (database name, user and pass)
ASKER
Thanks gbshahaq
ASKER