Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create Table from recordset

Posted on 2006-12-01
7
Medium Priority
?
2,483 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1200 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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 800 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 85
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

661 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