problem using VBA to update an external linked dbase table

G

George M

Hello,

I am trying to update a linked dbase table using VBA but
the cursor type is changed from dynamic to static when
VBA writes to the recordset. When I use the sql manually
in a query it updates the table. Any suggestions?

I am using Access XP (format Access 2000) and Jet 4.0

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.source = strSQL
rst.Open
arrInd = rst.GetRows
intCount = rst.RecordCount
Set rst = Nothing
'correct for Conakry
For x = 0 To 3
arrInd(intEff, x + 1) = arrInd(intEff, x) + arrInd
(intEff, x + 1)
arrInd(intPop, x + 1) = arrInd(intPop, x) + arrInd
(intPop, x + 1)
Next x
For x = 4 To intCount - 1
sgRatio = arrInd(intEff, x) / arrInd(intPop, x)
Select Case sgRatio
Case Is < 0.3
arrInd(intInd, x) = 1
Case Is < (0.5)
arrInd(intInd, x) = 2
Case Is < (0.8)
arrInd(intInd, x) = 3
Case Is >= (0.8)
arrInd(intInd, x) = 4

End Select
Debug.Print arrInd(1, x); " "; arrInd(3, x); " ";
arrInd(intInd, x)
Next x

DoCmd.TransferDatabase acImport, "dBASE
IV", "C:", , "Préfecture.dbf", "Temp"

Set rst = New ADODB.Recordset
strSQL = "Select temp.pre, temp.dep_07 from temp"
rst.ActiveConnection = CurrentProject.Connection
rst.source = strSQL
rst.CursorType = adOpenDynamic
rst.Open
While Not rst.EOF
x = 0
Done = False
While Not Done And x < 38
If rst(0) = cConakry Then
rst(1) = arrInd(intInd, 4)
Done = True
Else
If rst(0) = arrInd(intPre, x) Then
'up to the line above the record cursortype is dynamic
'when the line below executes the cursortype is changed
'to static
rst(1) = arrInd(intInd, x)
Done = True
Else
x = x + 1
End If
End If
Wend
rst.MoveNext
Wend

thanks
 
Top