RobertMidd
New member
- Local time
- Today, 01:58
- Joined
- Nov 20, 2019
- Messages
- 5
This is driving me nuts!!! and no amount of Google searching can find me a resolution.
In my Access form I can open a SQL database ok and pass a query and it gets executed ok (I know because it creates a new table and puts data into the table).
After I want to check for a record in the SQL database by selecting the first record in the table but the ADO recordset remain closed after I have opened it and I cannot check any result. The same select works ok in SSMS so I know a record is being returned.
So here is my code.
The MSGBox command with ObjRS returns 0 meaning it is closed and if I try to reference a property such as RecordCount then I get an error and the watch indicates <Operation is not allowed when the object is closed.>
Anyone any idea(s)?
Thanks, Robert.
In my Access form I can open a SQL database ok and pass a query and it gets executed ok (I know because it creates a new table and puts data into the table).
After I want to check for a record in the SQL database by selecting the first record in the table but the ADO recordset remain closed after I have opened it and I cannot check any result. The same select works ok in SSMS so I know a record is being returned.
So here is my code.
Code:
Private Sub DoSQLConnection(SQLText As String)
Dim ObjConn As Object
Dim StrConn As String
Dim ObjRS As Object
On Error GoTo NoConnect
Set ObjConn = New ADODB.Connection
Set ObjRS = New ADODB.Recordset
With ObjConn
StrConn = "Provider=SQLOLEDB;Data Source=" & MySQLServer & ";"
If MyAuthCon = 0 Then
StrConn = StrConn & "Trusted_Connection=Yes"
Else
StrConn = StrConn & "User id=" & MyUsercode & ";Password=" & MyPassword & ";"
End If
.ConnectionString = StrConn
.Open
Set ObjRS = .Execute(SQLText)
ObjRS.Open
MsgBox (ObjRS.State)
End With
Exit Sub
NoConnect:
MyConnError = 2
Screen.MousePointer = 1
Exit Sub
End SubAnyone any idea(s)?
Thanks, Robert.
Last edited:
