type mismatch error '13'????
Quote:
> Here is my code that i am currently using. This code
> worked without a hitch when i was using Access 97. I
> have now upgraded to Access XP. I get an error on
> the "Set tk....." line. this code appends some records
> from an Access table to a SQL table. Anyone have a clue?
> Function Export3()
> Dim db As Database
> Dim tk As Recordset
> Dim hte As Recordset
> Dim idx, response As Integer
> Set db = DBEngine.Workspaces(0).Databases(0)
> Set tk = db.OpenRecordset("SELECT ASIHTEAppend.XUICST,
> ASIHTEAppend.XUICGC, ASIHTEAppend.XUIDTE,
> ASIHTEAppend.XUITME, ASIHTEAppend.XUIAMT,
> ASIHTEAppend.XUIDES FROM ASIHTEAppend;")
> Set hte = db.OpenRecordset("HTEDTA_MR785AP",
> dbOpenDynaset, dbOpenDynaset, dbOptimistic)
> tk.MoveFirst
> Do While Not tk.EOF
> hte.AddNew
> For idx = 0 To 5
> hte(idx) = tk(idx)
> Next
> hte.Update
> tk.MoveNext
> Loop
> 'response = MsgBox("Transfer Complete", , "ASI to HTE")
> tk.Close
> hte.Close
> End Function
By default Access 2000 uses ADO, whereas 97 uses DAO. Make sure you have
a reference set to DAO and explicitly declare the relevant variables as
DAO, i.e. (I've included a few extra notes too)
Function Export3()
Dim db As DAO.Database
Dim tk As DAO.Recordset
Dim hte As DAO.Recordset
Dim idx AS Integer, response As Integer 'NOTE: Each variable must be
individually datatyped, otherwise it will default to Variant
'Set db = DBEngine.Workspaces(0).Databases(0) 'deprecated
Set db = CurrentDb
Set tk = db.OpenRecordset("SELECT ASIHTEAppend.XUICST,
ASIHTEAppend.XUICGC, ASIHTEAppend.XUIDTE,
ASIHTEAppend.XUITME, ASIHTEAppend.XUIAMT,
ASIHTEAppend.XUIDES FROM ASIHTEAppend;")
Set hte = db.OpenRecordset("HTEDTA_MR785AP",dbOpenDynaset,
dbOptimistic)
tk.MoveFirst
Do While Not tk.EOF
hte.AddNew
For idx = 0 To 5
hte(idx) = tk(idx)
Next
hte.Update
tk.MoveNext
Loop
'response = MsgBox("Transfer Complete", , "ASI to HTE")
tk.Close
hte.Close
'GOOD PRACTICE TO SET OBJECT VARIABLES TO NOTHING
Set tk = Nothing
Set hte = Nothing
Set db = Nothing
End Function
--
Tony Oakley (Microsoft Access MVP)
RF900RT
