orbitus
asked on
Create Table from recordset
I have found some code on EE and placed it together but I cant figure out why I am getting a type mismatch error. Hopefully someone will see and be able to help...
Sub connectExchange()
Dim cnn1 As ADODB.Connection
Dim cmdExeproc As ADODB.Command
Dim rstRecords As ADODB.Recordset
Dim strcnn As String
Dim varTest As Variant
Set cnn1 = New ADODB.Connection
'cnn1.Provider = "ExOLEDB.DataSource"
'strcnn = "Provider=Microsoft.JET.OL EDB.4.0;Ex change 4.0;" & _
"MAPILEVEL=Outlook Address Book\;TABLETYPE=1;" & _
"DATABASE={B1C82C96-7149-4 EDD-A709-8 D7E6651833 2}; PROFILE=cgk0o7;"
strcnn = "Provider=Microsoft.JET.OL EDB.4.0;Ex change 4.0;MAPILEVEL=Mailbox - GPM Job Request|;PROFILE=Outlook;T ABLETYPE=0 ;TABLENAME =Archive DO NOT PROCESS;DATABASE=C:\DOCUME ~1\cgk0o7\ LOCALS~1\T emp\;"
cnn1.Open strcnn
cnn1.CursorLocation = adUseClient
Set cmdExeproc = New ADODB.Command
cmdExeproc.ActiveConnectio n = cnn1
cmdExeproc.CommandText = "SELECT * FROM [Archive DO NOT PROCESS]"
'Set cmdExeproc.ActiveConnectio n = cnn1
'Set rstRecords = cmdExeproc.Execute
'cmdExeproc.ActiveConnecti on = Nothing
Set rstRecords = New ADODB.Recordset
rstRecords.CursorLocation = adUseClient
rstRecords.Open cmdExeproc, , adOpenKeyset, adLockReadOnly
If rstRecords.EOF = True And rstRecords.BOF = True Then
MsgBox ("There are no records meeting the specified criteria.")
Exit Sub
Else
varTest = rstRecords.GetRows()
'Kill "c:\test.xml"
'rstRecords.Save "c:\test.xml", adPersistXML
'Application.ImportXML "c:\test.xml", acStructureAndData
AddToTable (rstRecords) '<<<<<<<<<<<<<<<<<<<<<<<<< <<<<< Here is where I get the error "Type mismatch"
Debug.Print "Got em!"
End If
rstRecords.Close
Set rstRecords = Nothing
cnn1.Close
Set cnn1 = Nothing
Set cmdExeproc = Nothing
End Sub
Public Function AddToTable(adoRec As ADODB.Recordset)
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
'first clear out any old data in the temp table
CurrentDb.Execute "DELETE * FROM tblTemp"
'now open a recordset of that table so you can add new records
Set rst = New ADODB.Recordset
rst.Open Source:="SELECT * FROM tblTemp", _
ActiveConnection:=CurrentP roject.Con nection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic
'now loop through the recordset you passed in and add
'each record to the temp table
adoRec.MoveFirst
Do While Not adoRec.EOF
rst.AddNew
For Each fld In adoRec.Fields
rst.Fields(fld.Name).Value = fld.Value
Next
rst.Update
adoRec.MoveNext
Loop
'cleanup
rst.Close
Set rst = Nothing
End Function
Sub connectExchange()
Dim cnn1 As ADODB.Connection
Dim cmdExeproc As ADODB.Command
Dim rstRecords As ADODB.Recordset
Dim strcnn As String
Dim varTest As Variant
Set cnn1 = New ADODB.Connection
'cnn1.Provider = "ExOLEDB.DataSource"
'strcnn = "Provider=Microsoft.JET.OL
"MAPILEVEL=Outlook Address Book\;TABLETYPE=1;" & _
"DATABASE={B1C82C96-7149-4
strcnn = "Provider=Microsoft.JET.OL
cnn1.Open strcnn
cnn1.CursorLocation = adUseClient
Set cmdExeproc = New ADODB.Command
cmdExeproc.ActiveConnectio
cmdExeproc.CommandText = "SELECT * FROM [Archive DO NOT PROCESS]"
'Set cmdExeproc.ActiveConnectio
'Set rstRecords = cmdExeproc.Execute
'cmdExeproc.ActiveConnecti
Set rstRecords = New ADODB.Recordset
rstRecords.CursorLocation = adUseClient
rstRecords.Open cmdExeproc, , adOpenKeyset, adLockReadOnly
If rstRecords.EOF = True And rstRecords.BOF = True Then
MsgBox ("There are no records meeting the specified criteria.")
Exit Sub
Else
varTest = rstRecords.GetRows()
'Kill "c:\test.xml"
'rstRecords.Save "c:\test.xml", adPersistXML
'Application.ImportXML "c:\test.xml", acStructureAndData
AddToTable (rstRecords) '<<<<<<<<<<<<<<<<<<<<<<<<<
Debug.Print "Got em!"
End If
rstRecords.Close
Set rstRecords = Nothing
cnn1.Close
Set cnn1 = Nothing
Set cmdExeproc = Nothing
End Sub
Public Function AddToTable(adoRec As ADODB.Recordset)
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
'first clear out any old data in the temp table
CurrentDb.Execute "DELETE * FROM tblTemp"
'now open a recordset of that table so you can add new records
Set rst = New ADODB.Recordset
rst.Open Source:="SELECT * FROM tblTemp", _
ActiveConnection:=CurrentP
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic
'now loop through the recordset you passed in and add
'each record to the temp table
adoRec.MoveFirst
Do While Not adoRec.EOF
rst.AddNew
For Each fld In adoRec.Fields
rst.Fields(fld.Name).Value
Next
rst.Update
adoRec.MoveNext
Loop
'cleanup
rst.Close
Set rst = Nothing
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yep
You can shorten your opens also
rst.Open "SELECT * FROM tblTemp;" , CurrentProject.connection, adOpenKeyset, adLockOptimistic
J
rst.Open "SELECT * FROM tblTemp;" , CurrentProject.connection,
J
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Arthur is, of course, correct ... a function should always return a value. In your case, I'd return a Boolean value if the AddToTable function completes successfully:
Public Function AddToTable(adoRec As ADODB.Recordset) As Boolean
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
'first clear out any old data in the temp table
CurrentDb.Execute "DELETE * FROM tblTemp"
'now open a recordset of that table so you can add new records
Set rst = New ADODB.Recordset
rst.Open Source:="SELECT * FROM tblTemp", _
ActiveConnection:=CurrentP roject.Con nection, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic
'now loop through the recordset you passed in and add
'each record to the temp table
adoRec.MoveFirst
Do While Not adoRec.EOF
rst.AddNew
For Each fld In adoRec.Fields
rst.Fields(fld.Name).Value = fld.Value
Next
rst.Update
adoRec.MoveNext
Loop
'cleanup
rst.Close
Set rst = Nothing
AddToTable = True
End Function
Then, where you call the funciton:
If AddToTable(rstRecords) Then MsgBox "Added the Records!"
Public Function AddToTable(adoRec As ADODB.Recordset) As Boolean
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
'first clear out any old data in the temp table
CurrentDb.Execute "DELETE * FROM tblTemp"
'now open a recordset of that table so you can add new records
Set rst = New ADODB.Recordset
rst.Open Source:="SELECT * FROM tblTemp", _
ActiveConnection:=CurrentP
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic
'now loop through the recordset you passed in and add
'each record to the temp table
adoRec.MoveFirst
Do While Not adoRec.EOF
rst.AddNew
For Each fld In adoRec.Fields
rst.Fields(fld.Name).Value
Next
rst.Update
adoRec.MoveNext
Loop
'cleanup
rst.Close
Set rst = Nothing
AddToTable = True
End Function
Then, where you call the funciton:
If AddToTable(rstRecords) Then MsgBox "Added the Records!"
ASKER
awsome thanks yall for the in depth and quick answer
Chris
Chris
glad to be of assistance, and LSMConsulting>> Thanks for the vote of confidence LOL
AW
AW