Solved

Create Table from recordset

Posted on 2006-12-01
7
2,466 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

856 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