DAO to ADO

  • Thread starter Thread starter Steven K
  • Start date Start date
S

Steven K

Hello,

I have the following that needs to be migrated to ADO. Any assistance would
be appreciated. Thanks, Steven

Sub DAO_ADO()

Dim dbMarket As Database, rsMarket As DAO.Recordset, qdMarket As
QueryDef

Set dbMarket = CurrentDb
Set qdMarket = dbMarket.CreateQueryDef("", strSQL)
qdMarket.Execute

Set rsMarket = CurrentDb.OpenRecordset("tblManager_List")
With rsMarket
Do Until .EOF
strChain = rsMarket!PrintRange
ctrLen = Len(strChain)
rsMarket.Edit
rsMarket!PrintRange = "NoType"
rsMarket.Update
.MoveNext
Loop
.Close
End With
 
I made some changes that should help you, however without
seeing the "strSQL", I am only guessing what you are
trying to do. You can copy this code and paste it into a
module. ADO doesn't have a querydef method, so you need
to use docmd.runsql and then write an update statement.
You also need to referance ADO in your database.
T r a c e y @ C o x (dot) n e t


Sub DAO_ADORevised()

Dim rsMarket As ADODB.Recordset
'Dim qdMarket As QueryDef
Dim strSQL As String

Set rsMarket = New ADODB.Recordset
Set rsMarket.ActiveConnection =
CurrentProject.Connection


'Set qdMarket = dbMarket.CreateQueryDef("", strSQL)
'qdMarket.Execute

rsMarket.Open strSQL, CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
' you may need to change some of the constants to
suite your needs

rsMarket.MoveFirst

Do Until .EOF
strChain = rsMarket!PrintRange
ctrLen = Len(strChain)

'rsMarket.Edit
'rsMarket!PrintRange = "NoType"
'rsMarket.Update
' use the docmd.runSQL function, instead of the
bove lines, use the variables within the sql statment, the
sql statement should be similar to this:
DoCmd.RunSQL "UPDATE TableName.FieldName SET WHERE
(((TableName.PrintRange = " & """" & "No Type" & """"
& "));"

.MoveNext
Loop

rsMarket.Close
Set rsMarket = Nothing


end sub
 
Steven,

I have the feeling this can all be accomplished with an action query, either
through db.Execute or an ADO Command object. I can see what you're doing
with the recordset, but exactly what are you trying to do with the QueryDef?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Back
Top