How to speed up VBA Code ADODB Recordset updates
Please help I am new to VBA as you will see from my code. Probelm is i am trying to up date someone elses table that I can not alter. I Need to check if a row exists and if not add it. The code I have written works but very slow. They do not have a autonumber so i look up the max number first. I have a list of variables in my table the other table should have. So I read through them and then check if they exist if not add them.
Public Function AddVaribales(ProductRef As String)
Dim MaxRecord_New As Long
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim VariableID As Long
Dim ContentLevel As Integer
Dim Status As String
Dim nUseParentSetting As Integer
Dim sValue As String
nUseParentSetting = 0
sValue = "0"
MaxRecord_New = DMax("[nID]", "[UserDefinedProperties]") + 1
Set rst = New ADODB.Recordset
'Read what variables should in actinic
strSQL = "SELECT VariablesProductShouldHave.[nVariableID], VariablesProductShouldHave.[sName], VariablesProductShouldHave.[nContentLevel], VariablesProductShouldHave.[sStatus] FROM VariablesProductShouldHave;"
rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText
Do While Not rst.EOF
VariableID = rst("nVariableID")
ContentLevel = rst("nContentLevel")
Status = rst("sStatus")
AddRecords (ProductRef), (VariableID), (ContentLevel), (sStatus), (nUseParentSetting), (sValue)
rst.MoveNext
Loop
MsgBox "Update Complete...."
End Function
Public Function AddRecords(ProductRef As String, VariableID As Long, ContentLevel As Integer, Status As String, nUseParentSetting As Integer, sValue As String)
'Add records to table
Dim rst2 As ADODB.Recordset
Dim strSQL As String
'strSQL = "SELECT * From UserDefinedProperties WHERE nID=0"
' Check If It Exists first
strSQL = "SELECT UserDefinedProperties.*, UserDefinedProperties.[nVariableID], UserDefinedProperties.[sContentID] From UserDefinedProperties WHERE (((UserDefinedProperties.[nVariableID])=" & VariableID & ") AND ((UserDefinedProperties.[sContentID])='" & ProductRef & "'));"
Set rst2 = New ADODB.Recordset
rst2.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText
If rst2.EOF = True Then
rst2.Close
Set rst2 = Nothing
strSQL = "SELECT * From UserDefinedProperties WHERE nID=0"
Set rst2 = New ADODB.Recordset
rst2.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText
rst2.AddNew
rst2("nID") = MaxRecord_New
rst2("nUseParentSetting") = nUseParentSetting
rst2("sValue") = sValue
rst2("sContentID") = ProductRef
rst2("nContentLevel") = ContentLevel
rst2("sStatus") = Status
rst2("nVariableID") = VariableID
rst2.Update
MaxRecord_New = MaxRecord_New + 1
End If
rst2.Close
Set rst2 = Nothing
End Function
ASKER
Table 1 ' where i list the variables the other table should have
________________
ID VariableID VariableName
1 86968 Un-Printed
2 09709 Printed
Table 2 , Where the variables should be
nID nVariableID nUseParentSetting sValue sContentID nContentLevel sStatus
356 86968 0 £1.00 CA01 2 N
357 09709 0 £1.00 CA01 2 N
Table 3 list of Products
ProductRef Dec Price
CA01 Un-Printed £1.05
CA01 Printed 1 Colour £1.05
When I add a new product I Call AddVaribales(ProductRef)
Hope this makes sense
________________
ID VariableID VariableName
1 86968 Un-Printed
2 09709 Printed
Table 2 , Where the variables should be
nID nVariableID nUseParentSetting sValue sContentID nContentLevel sStatus
356 86968 0 £1.00 CA01 2 N
357 09709 0 £1.00 CA01 2 N
Table 3 list of Products
ProductRef Dec Price
CA01 Un-Printed £1.05
CA01 Printed 1 Colour £1.05
When I add a new product I Call AddVaribales(ProductRef)
Hope this makes sense
Open rst2 first with the full table.
Find the highest ID.
Run the loop.
For every record in rst, use Find to locate the ID.
If not found, add the record using next ID.
When the loop is finished, close both recordsets.
/gustav
Find the highest ID.
Run the loop.
For every record in rst, use Find to locate the ID.
If not found, add the record using next ID.
When the loop is finished, close both recordsets.
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Thanks I have done this through sql now, but thank you at least I know how to do it in future.
This should be able to be done in one. Can you give some sample data and how you call these functions?