Solved

Create Table from recordset

Posted on 2006-12-01
7
2,457 Views
Last Modified: 2012-05-05
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.OLEDB.4.0;Exchange 4.0;" & _
"MAPILEVEL=Outlook Address Book\;TABLETYPE=1;" & _
"DATABASE={B1C82C96-7149-4EDD-A709-8D7E66518332}; PROFILE=cgk0o7;"
    strcnn = "Provider=Microsoft.JET.OLEDB.4.0;Exchange 4.0;MAPILEVEL=Mailbox - GPM Job Request|;PROFILE=Outlook;TABLETYPE=0;TABLENAME=Archive DO NOT PROCESS;DATABASE=C:\DOCUME~1\cgk0o7\LOCALS~1\Temp\;"

    cnn1.Open strcnn
    cnn1.CursorLocation = adUseClient
   
    Set cmdExeproc = New ADODB.Command
    cmdExeproc.ActiveConnection = cnn1
    cmdExeproc.CommandText = "SELECT * FROM [Archive DO NOT PROCESS]"
   
    'Set cmdExeproc.ActiveConnection = cnn1
    'Set rstRecords = cmdExeproc.Execute
    'cmdExeproc.ActiveConnection = 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:=CurrentProject.Connection, _
             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
0
Comment
Question by:orbitus
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
ID: 18053622
Try

AddToTable rstRecords
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18053679
yep
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18053688
You can shorten your opens also

rst.Open "SELECT * FROM tblTemp;" , CurrentProject.connection, adOpenKeyset, adLockOptimistic

J
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 200 total points
ID: 18053848
a couple of comments on your code.

You have defined AddToTable as a Function:

Public Function AddToTable(adoRec As ADODB.Recordset)

but a Function is a procedure that returns some knind of value ( a Sub is a procedure that does some work, but does not return an actual value)  and AddToTable does not return a value.  It should be coded as a Sub procedure - this does not make ANY chanfge in the code, just in the declaration of the name of the Procedure:

Public Sub AddToTable(adoRec As ADODB.Recordset)
          |||

Also, when passing arguments to a Sub (or when calling a Function but you are NOT going to make use of the returned Value), in Access VBA, you should NOT enclose the Arguments in (...)

the (...) will cause the VB processor to 'interpret/evaluate' the argument, before passing the result to the procedure, which is why you were haing the problem, and why LSMConsulting make the suggestion that he made, above.

AW
0
 
LVL 84
ID: 18053935
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:=CurrentProject.Connection, _
             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!"

0
 
LVL 4

Author Comment

by:orbitus
ID: 18053954
awsome thanks yall for the in depth and quick answer

Chris
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18055299
glad to be of assistance, and LSMConsulting>> Thanks for the vote of confidence  LOL

AW
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now