ADO Connection producing run time Error 3001 arguments are of wrong type (1 Viewer)

bconner

Registered User.
Local time
Today, 13:30
Joined
Dec 22, 2008
Messages
183
Hello,
The runtime error 3001 is being generated on the line
Code:
ADOrs.lockType = adLockReadOnly
I have tried several of the locktypes and they are produce the same error. I can't seem to figure out what I am doing wrong. I am using late binding so I can learn how to do this.

Code:
Public Sub ADOtest()
Dim ADOconn As Object
Dim ADOrs As Object


Set ADOconn = CreateObject("ADODB.CONNECTION")
Set ADOrs = CreateObject("adodb.recordset")



   ADOconn.ConnectionString = "Driver={SQL Server};Server=VA10PWVSQL353\SQL01,10001;Database=CRA;Trusted_Connection=Yes;"
   ADOconn.Open
  
   Do While ADOconn.state <> 1
    DoEvents
    If ADOconn.state = 0 Then
       Exit Sub
    End If
    
   Loop
'Debug.Print ADOconn.state

Set ADOrs.activeconnection = ADOconn
 ADOrs.lockType = adLockReadOnly
 ADOrs.Source = "select * from zzzzCMS_DIY_DIAG"
 ADOrs.Open
 
If Not ADOrs.EOF Then ADOrs.MoveFirst

Do Until ADOrs.EOF
 Debug.Print ADOrs("ICD")
 ADOrs.MoveNext
Loop

 ADOconn.Close
 ADOrs.Close
 Set ADOconn = Nothing
 Set ADOrs = Nothing
 

End Sub
 

Isaac

Lifelong Learner
Local time
Today, 11:30
Joined
Mar 14, 2017
Messages
8,738
Might need to use numerical value of constant due to late binding. (1, I think)
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 05:30
Joined
Jan 20, 2009
Messages
12,849
Might need to use numerical value of constant due to late binding. (1, I think)

Or define the enumerated value for the name.
I included a module in the apps where I use ADO where I declare all the enumerations.
 

bconner

Registered User.
Local time
Today, 13:30
Joined
Dec 22, 2008
Messages
183
Or define the enumerated value for the name.
I included a module in the apps where I use ADO where I declare all the enumerations.
Thanks for the information Galaxiom.....
 

Isaac

Lifelong Learner
Local time
Today, 11:30
Joined
Mar 14, 2017
Messages
8,738
Thank you Isaac that worked! I appreciate your help...
Any time. You mentioned for learning purposes; I'll share that for me anyway, a lot of times what works nicely is to use early binding while developing. That way intellisense provides me valuable information about the methods, properties etc....that environment is essentially the 'learning' for me (and superior convenience, by a long shot). Then, if I decide to use late binding, it will be a switch to late binding just prior to deployment.....that environment is best for a wider user base and reduces the amount I have to worry about references, versions, etc (not always, just a general statement).

As Galaxiom said, it's extra-nice to declare constants or variables that define things to look the same way the early-bound argument values would, that way the code is easier to understand. While at the same time, (maybe), still taking advantage of late binding..if you wanted to.
 

bconner

Registered User.
Local time
Today, 13:30
Joined
Dec 22, 2008
Messages
183
Any time. You mentioned for learning purposes; I'll share that for me anyway, a lot of times what works nicely is to use early binding while developing. That way intellisense provides me valuable information about the methods, properties etc....that environment is essentially the 'learning' for me (and superior convenience, by a long shot). Then, if I decide to use late binding, it will be a switch to late binding just prior to deployment.....that environment is best for a wider user base and reduces the amount I have to worry about references, versions, etc (not always, just a general statement).

As Galaxiom said, it's extra-nice to declare constants or variables that define things to look the same way the early-bound argument values would, that way the code is easier to understand. While at the same time, (maybe), still taking advantage of late binding..if you wanted to.

That makes sense and I like the idea of creating a module and declaring all enumerations.
 

Isaac

Lifelong Learner
Local time
Today, 11:30
Joined
Mar 14, 2017
Messages
8,738
That makes sense and I like the idea of creating a module and declaring all enumerations.
Galaxiom's reminder has inspired me to start doing it more diligently myself. Up to now before I deploy I sometimes end up with code like this:

Code:
set ts = fso.opentextfile(strFilePath,8) ' ForAppending

With a little comment indicating what the value would have been. But the enumerations is a nice idea, especially for frequently used things!
I used to have a bookmark to a webpage that listed in plain text ALL Excel enums with their values, you could copy & paste the entire thing into a VBA module, but can't find it now.
 

Users who are viewing this thread

Top Bottom