Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
ADO recordset is nothing
Can someone tell me when a recordset will return 'nothing' and when a recordset will return no record (i.e. both BOF and EOF are true)? It appears for some of my procedures the recordsets return NOTHING sometimes and for some other routines they returns BOF and EOF. To avoid the confusion, I use the routine as follow:
...
dim rs as adodb.recordset
set rs = createobject("ADODB.record
...
rs.open mySQLstatement, adoConn
..
If not rs is nothing then
do while not (rs.eof or rs.bof)
xxx
xxx
loop
else
err.raise xxxxxx
end if
It is OK but I find it a little bit too cumbersome. Is there a better way to handle it?
Thanks in advance,
Wai-man
...
dim rs as adodb.recordset
set rs = createobject("ADODB.record
...
rs.open mySQLstatement, adoConn
..
If not rs is nothing then
do while not (rs.eof or rs.bof)
xxx
xxx
loop
else
err.raise xxxxxx
end if
It is OK but I find it a little bit too cumbersome. Is there a better way to handle it?
Thanks in advance,
Wai-man
Asked:
-
4
-
2
- +4
1 Solution
By creating the recordset with your statement
set rs = createobject("ADODB.record
will ensure that the recordset object exists (i.e. it is not Nothing)
When the recordset is opened the BOF and EOF properties will reflect the state of the current data in the recordset.
The only time I'm aware of a recordset being Nothing is when using ADO to return multiple recordsets and when the last recordset has been processed and
Set newrs = rs.NextRecordset
the newrs object is returned as Nothing
Hope this helps to shed some light
Regards
GK
set rs = createobject("ADODB.record
will ensure that the recordset object exists (i.e. it is not Nothing)
When the recordset is opened the BOF and EOF properties will reflect the state of the current data in the recordset.
The only time I'm aware of a recordset being Nothing is when using ADO to return multiple recordsets and when the last recordset has been processed and
Set newrs = rs.NextRecordset
the newrs object is returned as Nothing
Hope this helps to shed some light
Regards
GK
Try this,
if recordset.State = adStateOpen then
if recordset.recordcount > 0 then
xxx
xxx
xxx
end if
end if
if recordset.State = adStateOpen then
if recordset.recordcount > 0 then
xxx
xxx
xxx
end if
end if
In simple terms RS is Nothing if the SQL was faulty. But because you used the set rs = createobject("ADODB.record
A method for avoiding this problem is to use the connection object to create the recordset. I have inclduded some samples how to do this below.
Once you have opened a recordset you can ignore the the BOF property beacse you will be positioned at the first record or at the end of the file.
I am sure you will agree that as a computer programmer your objectives should be to receive zero support calls.
I access ADO through a class module to make life simple and nerver get bugs.
I called the class ADOSubLib and will post the code below.
First create a class module then paste the class below and save as ADOSubLib.CLS in your common project conents folder where you save the items which you add to all projects.
To use the code. The the ADOSubLib.cls to your project. In your form declarations you need the following:
Dim ADO as New ADOSubLib
In any sub you can then say:
Dim SQL as string
Dim CN as ADODB.Connection
Dim RS as ADODB.Recordset
Dim Ok as Boolean
' In your formload you can register a connection string to SQL oracle or Access database.
Here is a sample form load event:
Private Sub Form_Load()
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim SQL$
Dim Ok
' Register your ODBC connection string
ADO.Value = "driver={SQL Server};server=MyServer;ui
' or
ADO.RegisterConnectionStri
' or to connect to an access database
ADO.MDBConnect ("MyDB.MDB")
Ok = ADO.ConnectionOK(CN)
If Not Ok Then
MsgBox ADO.GetLastError(CN) + vbCrLf + "Connection: " + ADO.Value, vbExclamation, "ODBC Connection Error"
Else
MsgBox "Connected OK"
End If
End Sub
'=========================
Here is a read recordset sample:
Private Sub Command1_Click()
Dim SQL As String
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim Ok As Boolean
' In your formload you can register a connection string to SQL oracle or Access database.
e.g.
ADO.RegisterConnectionStri
' or to connect toan access database
ADO.MDBConnect ("MyDB.MDB")
' to open a connection:
Ok = ADO.ConnectOK(CN)
If Not Ok Then
MsgBox ADO.GetLastError(CN) + vbCrLf + "Connect String:" + ADO.Value, vbExclamation, "Database Connection Error"
Exit Sub
End If
' To Read read only recordset
SQL = "Select * From [YourTable];"
Ok = ADO.OpenRSROOK(CN, RS, SQL)
If Not Ok Then
MsgBox ADO.GetLastError(CN) + vbCrLf + "SQL:" + SQL, vbExclamation, "SQL Error"
Set CN = Nothing
Exit Sub
End If
' Optional handle for no records
If RS.EOF Then
MsgBox "No Records"
Exit Sub
End If
' skips if there are no records in recordset
Do While Not RS.EOF
' process record
RS.MoveNext
Loop
' Close record set
RS.Close
Set RS = Nothing
cs.Close
Set cs = Nothing
End Sub
'=========================
Here is the code for the ADOSubLib class
Option Explicit
Dim DefaultODBCConnect As String
Function ConnectionODBCOK(Connectio
' Allows connection to any database
' Create a connection object
Set ConnectionObj = New ADODB.Connection
' Setup the connection params
ConnectionObj.ConnectionSt
ConnectionObj.CommandTimeo
ConnectionObj.ConnectionTi
' Make sure no errors
On Error Resume Next
Err.Clear
ConnectionObj.Open ' try to open the connection
If Err.Number <> 0 Then
ConnectionODBCOK = False
'qw "Connection Error: " & CStr(Err.Number) & " " & Err.Description & " ODBC: " & ODBCConnect
Else
ConnectionODBCOK = True
End If
End Function
Function ConnectionOK(ConnectionObj
' Connect to the registered connection
ConnectionOK = ConnectionODBCOK(Connectio
End Function
Function ExecuteSQLOK(SQL As String) As Boolean
' Will execute an SQL Command
' Retruns True/False error status Fales=Fail
Dim CN As ADODB.Connection ' Setup a connection object
Dim Ok
Ok = ConnectionOK(CN)
If Not Ok Then
ExecuteSQLOK = False
Else
On Error Resume Next
Err.Clear
CN.Execute SQLCheck(SQL) ' Run the command
If Err.Number <> 0 Then
ExecuteSQLOK = True
Else
ExecuteSQLOK = False
End If
On Error GoTo 0
CN.Close
End If
Set CN = Nothing
End Function
Public Function InsertGetCounter(CN As ADODB.Connection, SQLforInsertInto As String) As Boolean
' Fires an Insert Into satement then gets the Indetity/autoincreament of the last error
Dim RS As Recordset
Dim SQL$
SQL$ = "SET NOCOUNT ON;" + SQLforInsertInto
If Right$(SQL, 1) <> ";" Then
SQL = SQL + ";"
End If
SQL$ = SQL$ + " Select @@Identity as RecID;SET NOCOUNT OFF;"
On Error Resume Next
Err.Clear
Set RS = CN.Execute(SQL$)
If Err.Number = 0 Then
InsertGetCounter = RS("RecID")
RS.Close
Else
InsertGetCounter = 0
End If
Set RS = Nothing
End Function
Public Function cSQL(SQLData As String)
' Puts a string into an Insert SQL satement
' removes ' character which will cause crash.
Dim SQLRep As String 'String in Error Routine
Dim iSLen As Integer 'String Length in Error Routine
Dim cCHar As String
Dim iCharCo As Integer
SQLRep = ""
iSLen = Len(SQLData)
For iCharCo = 1 To iSLen
cCHar = Mid(SQLData, iCharCo, 1)
SQLRep = SQLRep & cCHar
If cCHar = "'" Then SQLRep = SQLRep & cCHar
If cCHar = """" Then SQLRep = SQLRep & cCHar
Next iCharCo
cSQL = SQLRep
End Function
Function GetADOCounter(CN As Connection) As Long
' Return the indetity/autonumber of the last inserted record.
' Warning if triggers add record to other tables this work
' It is faster to use the InsertGetCounter routine.
Dim stRSQL$
Dim rsNewAutoIncrement As ADODB.Recordset
stRSQL = "SELECT @@Identity"
On Error Resume Next
Set rsNewAutoIncrement = New ADODB.Recordset
rsNewAutoIncrement.Open stRSQL, CN, adOpenForwardOnly, _
adLockReadOnly, adCmdText
GetADOCounter = rsNewAutoIncrement.Fields(
rsNewAutoIncrement.Close
If Err.Number <> 0 Then GetADOCounter = 0
End Function
Public Function GetLastError(CN As ADODB.Connection) As String
' Obtains the last error on a connection
Dim M$
Dim E As ADODB.Error
Dim Elist As ADODB.Errors
Set Elist = CN.Errors
For Each E In Elist
M$ = M$ + CStr(E.Number) + " " + E.Description + " " + E.Source + " " + E.SQLState + vbCrLf
Next
GetLastError = M$
End Function
Public Sub RegisterConnectString(Conn
' Regiser an ODBC connection string
DefaultODBCConnect = ConnectionString
End Sub
Public Property Let MDBConnect(AccessDatabaseN
' Sets up a connection string for an access database
DefaultODBCConnect$ = "Provider=Microsoft.Jet.OL
End Property
Function OpenRSOK(CN As ADODB.Connection, RS As ADODB.Recordset, SQL As String, pCursorType As ADODB.CursorLocationEnum, pLockType As ADODB.CursorTypeEnum) As Boolean
' Open a recordset
' Warning CN Must have not been used for anything other then a connection
' is CN is nothing then a new connection will be opened
' usage example
'OK = OpenRSOK(CN, RS, "Select * from [Test];", adUseServer, adOpenDynamic)
Dim Ok
Dim didopen
didopen = False
' Open The Connection
If CN Is Nothing Then
Ok = ConnectionOK(CN)
If Not Ok Then
OpenRSOK = False
Exit Function
End If
didopen = True
End If
On Error Resume Next
Err.Clear
Set RS = New ADODB.Recordset
RS.Open SQLCheck(SQL), CN, pCursorType, pLockType ', padcmdtext
If Err.Number <> 0 Then
' qw "OpenRSOK Error: " & CStr(Err.Number) & " " & Err.Description & " " & SQLCheck(SQL)
OpenRSOK = False
If didopen Then
CN.Close
Set CN = Nothing
Set RS = Nothing
End If
Else
OpenRSOK = True
End If
End Function
Function OpenRSROOK(CN As ADODB.Connection, RS As ADODB.Recordset, SQL As String) As Boolean
' Open a readonly record set
OpenRSROOK = OpenRSOK(CN, RS, SQL, adOpenForwardOnly, adLockReadOnly)
End Function
Public Function Prep(StringtoPrepare As String)
' This function is oibsolete and has been replaced by cSQL
Prep = "'" + Replace(StringtoPrepare, "'", "^", 1, -1, vbBinaryCompare) + "'"
End Function
Public Function PrepDate(RequiredDate As Date)
' Converts a date/time to SQL server format placing within single quotes
' Example:
' SQL=SQL + "InsertDate = "+ADO.PreDate(Now)
PrepDate = "'" + Format$(RequiredDate, "YYYY-MM-DD HH:Nn:SS") + "'"
End Function
Public Function SQLCheck(SQL As String) As String
' Place sql syntac check here
' or Auto conversion from SQL to Oracle etc.
SQLCheck = SQL
End Function
Public Property Let Value(DefaultConnectionStr
' Make this the Default Procedure (see tools default attributes)
DefaultODBCConnect$ = DefaultConnectionString
End Property
Public Property Get Value() As String
Value = DefaultODBCConnect$
End Property
A method for avoiding this problem is to use the connection object to create the recordset. I have inclduded some samples how to do this below.
Once you have opened a recordset you can ignore the the BOF property beacse you will be positioned at the first record or at the end of the file.
I am sure you will agree that as a computer programmer your objectives should be to receive zero support calls.
I access ADO through a class module to make life simple and nerver get bugs.
I called the class ADOSubLib and will post the code below.
First create a class module then paste the class below and save as ADOSubLib.CLS in your common project conents folder where you save the items which you add to all projects.
To use the code. The the ADOSubLib.cls to your project. In your form declarations you need the following:
Dim ADO as New ADOSubLib
In any sub you can then say:
Dim SQL as string
Dim CN as ADODB.Connection
Dim RS as ADODB.Recordset
Dim Ok as Boolean
' In your formload you can register a connection string to SQL oracle or Access database.
Here is a sample form load event:
Private Sub Form_Load()
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim SQL$
Dim Ok
' Register your ODBC connection string
ADO.Value = "driver={SQL Server};server=MyServer;ui
' or
ADO.RegisterConnectionStri
' or to connect to an access database
ADO.MDBConnect ("MyDB.MDB")
Ok = ADO.ConnectionOK(CN)
If Not Ok Then
MsgBox ADO.GetLastError(CN) + vbCrLf + "Connection: " + ADO.Value, vbExclamation, "ODBC Connection Error"
Else
MsgBox "Connected OK"
End If
End Sub
'=========================
Here is a read recordset sample:
Private Sub Command1_Click()
Dim SQL As String
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim Ok As Boolean
' In your formload you can register a connection string to SQL oracle or Access database.
e.g.
ADO.RegisterConnectionStri
' or to connect toan access database
ADO.MDBConnect ("MyDB.MDB")
' to open a connection:
Ok = ADO.ConnectOK(CN)
If Not Ok Then
MsgBox ADO.GetLastError(CN) + vbCrLf + "Connect String:" + ADO.Value, vbExclamation, "Database Connection Error"
Exit Sub
End If
' To Read read only recordset
SQL = "Select * From [YourTable];"
Ok = ADO.OpenRSROOK(CN, RS, SQL)
If Not Ok Then
MsgBox ADO.GetLastError(CN) + vbCrLf + "SQL:" + SQL, vbExclamation, "SQL Error"
Set CN = Nothing
Exit Sub
End If
' Optional handle for no records
If RS.EOF Then
MsgBox "No Records"
Exit Sub
End If
' skips if there are no records in recordset
Do While Not RS.EOF
' process record
RS.MoveNext
Loop
' Close record set
RS.Close
Set RS = Nothing
cs.Close
Set cs = Nothing
End Sub
'=========================
Here is the code for the ADOSubLib class
Option Explicit
Dim DefaultODBCConnect As String
Function ConnectionODBCOK(Connectio
' Allows connection to any database
' Create a connection object
Set ConnectionObj = New ADODB.Connection
' Setup the connection params
ConnectionObj.ConnectionSt
ConnectionObj.CommandTimeo
ConnectionObj.ConnectionTi
' Make sure no errors
On Error Resume Next
Err.Clear
ConnectionObj.Open ' try to open the connection
If Err.Number <> 0 Then
ConnectionODBCOK = False
'qw "Connection Error: " & CStr(Err.Number) & " " & Err.Description & " ODBC: " & ODBCConnect
Else
ConnectionODBCOK = True
End If
End Function
Function ConnectionOK(ConnectionObj
' Connect to the registered connection
ConnectionOK = ConnectionODBCOK(Connectio
End Function
Function ExecuteSQLOK(SQL As String) As Boolean
' Will execute an SQL Command
' Retruns True/False error status Fales=Fail
Dim CN As ADODB.Connection ' Setup a connection object
Dim Ok
Ok = ConnectionOK(CN)
If Not Ok Then
ExecuteSQLOK = False
Else
On Error Resume Next
Err.Clear
CN.Execute SQLCheck(SQL) ' Run the command
If Err.Number <> 0 Then
ExecuteSQLOK = True
Else
ExecuteSQLOK = False
End If
On Error GoTo 0
CN.Close
End If
Set CN = Nothing
End Function
Public Function InsertGetCounter(CN As ADODB.Connection, SQLforInsertInto As String) As Boolean
' Fires an Insert Into satement then gets the Indetity/autoincreament of the last error
Dim RS As Recordset
Dim SQL$
SQL$ = "SET NOCOUNT ON;" + SQLforInsertInto
If Right$(SQL, 1) <> ";" Then
SQL = SQL + ";"
End If
SQL$ = SQL$ + " Select @@Identity as RecID;SET NOCOUNT OFF;"
On Error Resume Next
Err.Clear
Set RS = CN.Execute(SQL$)
If Err.Number = 0 Then
InsertGetCounter = RS("RecID")
RS.Close
Else
InsertGetCounter = 0
End If
Set RS = Nothing
End Function
Public Function cSQL(SQLData As String)
' Puts a string into an Insert SQL satement
' removes ' character which will cause crash.
Dim SQLRep As String 'String in Error Routine
Dim iSLen As Integer 'String Length in Error Routine
Dim cCHar As String
Dim iCharCo As Integer
SQLRep = ""
iSLen = Len(SQLData)
For iCharCo = 1 To iSLen
cCHar = Mid(SQLData, iCharCo, 1)
SQLRep = SQLRep & cCHar
If cCHar = "'" Then SQLRep = SQLRep & cCHar
If cCHar = """" Then SQLRep = SQLRep & cCHar
Next iCharCo
cSQL = SQLRep
End Function
Function GetADOCounter(CN As Connection) As Long
' Return the indetity/autonumber of the last inserted record.
' Warning if triggers add record to other tables this work
' It is faster to use the InsertGetCounter routine.
Dim stRSQL$
Dim rsNewAutoIncrement As ADODB.Recordset
stRSQL = "SELECT @@Identity"
On Error Resume Next
Set rsNewAutoIncrement = New ADODB.Recordset
rsNewAutoIncrement.Open stRSQL, CN, adOpenForwardOnly, _
adLockReadOnly, adCmdText
GetADOCounter = rsNewAutoIncrement.Fields(
rsNewAutoIncrement.Close
If Err.Number <> 0 Then GetADOCounter = 0
End Function
Public Function GetLastError(CN As ADODB.Connection) As String
' Obtains the last error on a connection
Dim M$
Dim E As ADODB.Error
Dim Elist As ADODB.Errors
Set Elist = CN.Errors
For Each E In Elist
M$ = M$ + CStr(E.Number) + " " + E.Description + " " + E.Source + " " + E.SQLState + vbCrLf
Next
GetLastError = M$
End Function
Public Sub RegisterConnectString(Conn
' Regiser an ODBC connection string
DefaultODBCConnect = ConnectionString
End Sub
Public Property Let MDBConnect(AccessDatabaseN
' Sets up a connection string for an access database
DefaultODBCConnect$ = "Provider=Microsoft.Jet.OL
End Property
Function OpenRSOK(CN As ADODB.Connection, RS As ADODB.Recordset, SQL As String, pCursorType As ADODB.CursorLocationEnum, pLockType As ADODB.CursorTypeEnum) As Boolean
' Open a recordset
' Warning CN Must have not been used for anything other then a connection
' is CN is nothing then a new connection will be opened
' usage example
'OK = OpenRSOK(CN, RS, "Select * from [Test];", adUseServer, adOpenDynamic)
Dim Ok
Dim didopen
didopen = False
' Open The Connection
If CN Is Nothing Then
Ok = ConnectionOK(CN)
If Not Ok Then
OpenRSOK = False
Exit Function
End If
didopen = True
End If
On Error Resume Next
Err.Clear
Set RS = New ADODB.Recordset
RS.Open SQLCheck(SQL), CN, pCursorType, pLockType ', padcmdtext
If Err.Number <> 0 Then
' qw "OpenRSOK Error: " & CStr(Err.Number) & " " & Err.Description & " " & SQLCheck(SQL)
OpenRSOK = False
If didopen Then
CN.Close
Set CN = Nothing
Set RS = Nothing
End If
Else
OpenRSOK = True
End If
End Function
Function OpenRSROOK(CN As ADODB.Connection, RS As ADODB.Recordset, SQL As String) As Boolean
' Open a readonly record set
OpenRSROOK = OpenRSOK(CN, RS, SQL, adOpenForwardOnly, adLockReadOnly)
End Function
Public Function Prep(StringtoPrepare As String)
' This function is oibsolete and has been replaced by cSQL
Prep = "'" + Replace(StringtoPrepare, "'", "^", 1, -1, vbBinaryCompare) + "'"
End Function
Public Function PrepDate(RequiredDate As Date)
' Converts a date/time to SQL server format placing within single quotes
' Example:
' SQL=SQL + "InsertDate = "+ADO.PreDate(Now)
PrepDate = "'" + Format$(RequiredDate, "YYYY-MM-DD HH:Nn:SS") + "'"
End Function
Public Function SQLCheck(SQL As String) As String
' Place sql syntac check here
' or Auto conversion from SQL to Oracle etc.
SQLCheck = SQL
End Function
Public Property Let Value(DefaultConnectionStr
' Make this the Default Procedure (see tools default attributes)
DefaultODBCConnect$ = DefaultConnectionString
End Property
Public Property Get Value() As String
Value = DefaultODBCConnect$
End Property
warning ashish_1319 proposes the use of the recordcount property which will not work. Recordcount is not mentioned in the ADODB recordset open documentation. However, EOF is. Furthermore, it is quite possible that ADO does not know how many records are in the recordset, in this case the recordcount is = -1
inthedark, I deleted your dup comment.
ashish_1319, I have rejected your answer as it clearly does not address the problem. Please try and use comments only, especially in a question where some discussion is taking place. You are not new here, I should not have to remind you of the etiquette of the VB topic area.
Lunchy
Friendly Neighbourhood Community Support Moderator
ashish_1319, I have rejected your answer as it clearly does not address the problem. Please try and use comments only, especially in a question where some discussion is taking place. You are not new here, I should not have to remind you of the etiquette of the VB topic area.
Lunchy
Friendly Neighbourhood Community Support Moderator
TO intheDark:
I am a bit dumb here but can you tell me what's the main difference between your code and my code, given that I am also using the adoConn object to open the recordset?
I am using Createobject because I am working on an ASP project and was told this is the correct way to initialise object for VB ActiveX DLL.
Hope this help.
Cheers,
Wai-man
I am a bit dumb here but can you tell me what's the main difference between your code and my code, given that I am also using the adoConn object to open the recordset?
I am using Createobject because I am working on an ASP project and was told this is the correct way to initialise object for VB ActiveX DLL.
Hope this help.
Cheers,
Wai-man
waiman you ask what is the difference in code?
Your code creates the object before the open is made and therefore if the open fails the object will still exists and so the recordset will never be nothing.
So your code may blow up with unhanded error.
The example I posted generates the record set from the Connection object. In this case the recordset will be nothing if the open fails.
I am not saying you should use the ADO object that I posted but to help you make a judgement here is a list of the advantages it provides:
1) Relieves tedium - as so nothing is cumbersome.
All I have to do is type "ADO." and I get a list of the functions that I need to use. I have made it very simple to open a readonly recordset and set up the standard recordset open parameters to autolist when you try to select them from the ADOSubLib class.
2) Error Handling
If you don't handle errors you get lots of phone calls, the more you get, the less you can earn. You need to set error traps if you want to make a good job of writing software. Using my ADO object handles errors for you so you just ask a simple question:
ok=ADO.OpenRSOK(CN,RS,SQL)
And to handle the error you can pass the SQL statement to a general error handle which will place you code on the clipboard so you can see what went wrong.. You can also pass a list of the errors from the connection object. You can get the list of errors by typing ADO.GetLastError(CN).
If Not Ok Then
HandleSQLErrors "Stage: 1510", ADO,GetLastError(CN), SQL
Exit Sub
End if
3) Future Proofing
You did not specify the Open method's options when opening your recordset. This can cause problems when you connect to a different data source. You can also get caught out when Microsoft changes its defaults which they can and will do at some time in the future.
4) Easy Conversion to New Technologies
By making only one or two places in your software talk to the ADO methods Connect & Open Recordset and Execute Command you can make it easy to convert to another system. We've seen DAO and now ADO, what's next? If you plane for a change now you will be better off in the future.
For example there may be minor changes with syntax between Oracle, IBM BD2, MYSQL, and SQL Server you can handle these differences simply as you only have to enhance a couple of subroutines rather than a whole application.
Others may share different views. My philosophy is based around retaining the ownership of your own software for decades, making it easy to support and quick to enhance. Hope this helps you......
Your code creates the object before the open is made and therefore if the open fails the object will still exists and so the recordset will never be nothing.
So your code may blow up with unhanded error.
The example I posted generates the record set from the Connection object. In this case the recordset will be nothing if the open fails.
I am not saying you should use the ADO object that I posted but to help you make a judgement here is a list of the advantages it provides:
1) Relieves tedium - as so nothing is cumbersome.
All I have to do is type "ADO." and I get a list of the functions that I need to use. I have made it very simple to open a readonly recordset and set up the standard recordset open parameters to autolist when you try to select them from the ADOSubLib class.
2) Error Handling
If you don't handle errors you get lots of phone calls, the more you get, the less you can earn. You need to set error traps if you want to make a good job of writing software. Using my ADO object handles errors for you so you just ask a simple question:
ok=ADO.OpenRSOK(CN,RS,SQL)
And to handle the error you can pass the SQL statement to a general error handle which will place you code on the clipboard so you can see what went wrong.. You can also pass a list of the errors from the connection object. You can get the list of errors by typing ADO.GetLastError(CN).
If Not Ok Then
HandleSQLErrors "Stage: 1510", ADO,GetLastError(CN), SQL
Exit Sub
End if
3) Future Proofing
You did not specify the Open method's options when opening your recordset. This can cause problems when you connect to a different data source. You can also get caught out when Microsoft changes its defaults which they can and will do at some time in the future.
4) Easy Conversion to New Technologies
By making only one or two places in your software talk to the ADO methods Connect & Open Recordset and Execute Command you can make it easy to convert to another system. We've seen DAO and now ADO, what's next? If you plane for a change now you will be better off in the future.
For example there may be minor changes with syntax between Oracle, IBM BD2, MYSQL, and SQL Server you can handle these differences simply as you only have to enhance a couple of subroutines rather than a whole application.
Others may share different views. My philosophy is based around retaining the ownership of your own software for decades, making it easy to support and quick to enhance. Hope this helps you......
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
Try this...
...
dim rs as adodb.recordset
set rs = createobject("ADODB.record
...
rs.open mySQLstatement, adoConn
...
while not rs.eof
xxx
xxx
rs.MoveNext
loop