Connection odbc and dns

[Jimgarry]

Hi, thanks in advance,

Im just beginning working with both VB and SQL so please bear with me.

I'm creating a vb6 project that is to update a table in Pervasive 2000i on netware server.
I can access the table just fine using a form, adodc connection on on the form using a dns connection. If I try to do the same with a script in vb I get runtime errors such as "Run-Time Error 3709 the connection cnannot be used to perform this operation. It is etither closed or invalid in this context. "


' Please note that I have tried the pervasive odbc in connection string. Also I have tried differnt ways with the recordset
Thank you for any assisstance
Jim

here is my code:


Option Explicit

Public CN As New ADODB.Connection


Public strClient As String
Public Sub Main()
Dim rs As New ADODB.Recordset
Dim strCNString As String
Dim strRSString As String
strCNString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=Accounting"
strRSString = "Select * from ACCT022403.DBO.APVNDCDL"
CN.Open strCNString
rs.Open "Select * from ACCT022403.DBO.APVNDCDL"
MsgBox rs.Fields("RecordKey&quot

Exit Sub

End Sub

 

[mirtheil]

Change your rs.open from:

 rs.Open "Select * from ACCT022403.DBO.APVNDCDL"

to:

 rs.Open "Select * from ACCT022403.DBO.APVNDCDL", CN

You need to make sure that the recordset open knows which connection to use.
Also, is the table name "ACCT022403.DBO.APVNDCDL"? If so, you probably should put it in double quotes like:

 rs.Open "Select * from " & chr$(34) & "ACCT022403.DBO.APVNDCDL" " & chr$(34), CN

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[Jimgarry]

Mirtheil,
Sorry I did not get back sooner, thanks for your assistance.
Jim Garry