Hi Hafeez,
To use ADO object, we may need to add Microsoft ActiveX Data Object 2.x
Library via tools->References; to use ADOX objects, we may need to add
Microsoft ADO Ext. 2.7 for Dll and Security via tools->References.
It is true Query is saved as query, it is for the query results we save
them to tables (for the results the query returns we can save them into a
temp table);
For resource about ADO, you may want to refer to the articles:
ActiveX Data Objects (ADO) Frequently Asked Questions
http://support.microsoft.com/default.aspx?scid=KB;EN-US;183606
Implementing ADO with Various Development Languages
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnado/html/
msdn_adorosest.asp
Please feel free to reply to the threads if you have any questions or
concerns.
Sincerely,
Alick Ye, MCSD
Product Support Services
Microsoft Corporation
Get Secure! - <
www.microsoft.com/security>
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Content-Class: urn:content-classes:message
| From: "Hafeez Esmail" <
[email protected]>
| X-Tomcat-NG: microsoft.public.access.modulesdaovba
|
| Hi Alick,
|
| A few questions:
| What's the name of the file I need to reference in order
| to use the ADO commands?(I can't find anything saying ADO
| or ADOX or ADODB)
| My query is being saved as a query not as a table. Does
| this make a difference/how do save it to a table?
|
| Thanks
| Hafeez Esmail
|
| >-----Original Message-----
| >
| >
| >Hi Hafeez,
| >
| >Yes, there are some ways. The query saves the results to
| a table, so the
| >question is how to compare the records in two tables. The
| general way is to
| >create two recordset objects connecting to the two tables
| separately, loop
| >the recordset and compare each row/column.
| >
| >To loop in recordset, code snipped:
| >
| > Do While Not rs.EOF
| >
| >'your code
| > rs.MoveNext
| >
| > Loop
| >
| >You may refer to the sample in my previous post for how
| to open/connect a
| >table with recordset object.
| >
| >
| >
| >Sincerely,
| >
| >Alick Ye, MCSD
| >Product Support Services
| >Microsoft Corporation
| >Get Secure! - <
www.microsoft.com/security>
| >
| >This posting is provided "AS IS" with no warranties, and
| confers no rights.
| >
| >
| >
| >
| >--------------------
| >| Content-Class: urn:content-classes:message
| >| X-Tomcat-NG: microsoft.public.access.modulesdaovba
| >|
| >| Thanks a lot Alick,
| >|
| >| There seems to be a Northwind example for everything :-
| ).
| >| Is there a way for comparing the results of this query
| to
| >| an existing table?
| >|
| >| Hafeez Esmail
| >|
| >| >-----Original Message-----
| >| >Hi Hafeez,
| >| >
| >| >If I haven't misunderstood your issue, we can try ADO
| >| RecordSet object to
| >| >filter the search results and save the search results
| to
| >| a temp table.
| >| >Below is a sample based on the sample northWind.mdb
| for
| >| your reference.
| >| >
| >| >Option Compare Database
| >| >Option Explicit
| >| >
| >| >Private Sub Command0_Click()
| >| >
| >| > Dim rs As ADODB.Recordset
| >| > Dim temprs As ADODB.Recordset
| >| >
| >| > Dim tbl As New ADOX.Table
| >| > Dim cat As New ADOX.Catalog
| >| >
| >| >
| >| > Set rs = New ADODB.Recordset
| >| >
| >| > With rs
| >| > .ActiveConnection =
| CurrentProject.Connection
| >| > .Source = "SELECT CompanyName, City FROM
| >| Customers"
| >| > .CursorType = adOpenKeyset
| >| > .CursorLocation = adUseClient
| >| > .LockType = adLockOptimistic
| >| > .Open
| >| > 'filter the records
| >| > .Filter = "City = 'Campinas'"
| >| > Debug.Print "Recordcount: " & .RecordCount
| >| >
| >| > End With
| >| >
| >| >
| >| > 'Return Reference to current database.
| >| > Set cat.ActiveConnection =
| CurrentProject.Connection
| >| >
| >| > 'Assign the new table name.
| >| >
| >| > With tbl
| >| > .Name = "temptable"
| >| >
| >| > ' Append new columns to the table.
| >| > With .Columns
| >| > .Append "MyID", adInteger
| >| > .Append "CompanyName", adWChar, 50
| >| >
| >| > 'After appending columns, set
| >| > 'provider specific properties.
| >| > With !MyID
| >| > Set .ParentCatalog = cat
| >| > .Properties("Autoincrement") =
| >| True
| >| > .Properties("seed") = CLng(1)
| >| > .Properties("increment") = CLng
| (1)
| >| > End With
| >| >
| >| > With !CompanyName
| >| >
| >| > Set .ParentCatalog = cat
| >| > .Properties("Jet
| >| OLEDB:Compressed " _
| >| > & "UniCode
| Strings")
| >| = True
| >| > End With
| >| >
| >| >
| >| > End With
| >| > End With
| >| >
| >| > ' Append new table to the provider catalog and
| clean
| >| up.
| >| > cat.Tables.Append tbl
| >| > Set cat = Nothing
| >| >
| >| > Set temprs = New ADODB.Recordset
| >| >
| >| > temprs.Open "temptable",
| CurrentProject.Connection,
| >| adOpenDynamic,
| >| >adLockOptimistic
| >| >
| >| > If Not rs.BOF Then
| >| >
| >| > rs.MoveFirst
| >| >
| >| > End If
| >| >
| >| >
| >| > Do While Not rs.EOF
| >| >
| >| > temprs.AddNew
| >| > temprs.Fields("CompanyName").Value = rs.Fields
| >| ("CompanyName")
| >| > temprs.Update
| >| >
| >| > rs.MoveNext
| >| >
| >| > Loop
| >| >
| >| > rs.Close
| >| > temprs.Close
| >| >
| >| > Set rs = Nothing
| >| > Set temprs = Nothing
| >| >
| >| >End Sub
| >| >
| >| >
| >| >A simpler way is to use a query like below, run this
| >| query will create a
| >| >temporarily table temptable to save the search
| results,
| >| you can create the
| >| >query dynamically to change the filter
| (city='campinas')
| >| each time and get
| >| >different searching results.
| >| >
| >| >SELECT * INTO temptable
| >| >FROM customers
| >| >WHERE city='Campinas';
| >| >
| >| >If you have any concerns or questions, please feel
| free
| >| reply to the
| >| >threads.
| >| >
| >| >
| >| >
| >| >Sincerely,
| >| >
| >| >Alick Ye, MCSD
| >| >Product Support Services
| >| >Microsoft Corporation
| >| >Get Secure! - <
www.microsoft.com/security>
| >| >
| >| >This posting is provided "AS IS" with no warranties,
| and
| >| confers no rights.
| >| >
| >| >
| >| >--------------------
| >| >| Content-Class: urn:content-classes:message
| >| >| From: "Hafeez Esmail" <
[email protected]>
| >| >| Sender: "Hafeez Esmail" <
[email protected]>
| >| >| X-Tomcat-NG: microsoft.public.access.modulesdaovba
| >| >|
| >| >| Hi Alick,
| >| >|
| >| >| I have a macro that runs a module. The piece of
| code
| >| >| written in my origingal mesage is part of that
| module.
| >| It
| >| >| gathers the Last name from one the rstname
| (recordset
| >| >| based on a table) and uses that as a search criteria
| >| >| against rstrec (recordset based on another table).
| >| >|
| >| >| I want to save the results as "Predef120" &
| strLastName
| >| >| where strLastName is the current Last Name of the
| >| person
| >| >| in question.
| >| >|
| >| >| One last criteria:
| >| >| I want to send an email only if the search result is
| >| has
| >| >| something new so I'd like the result to be compared
| to
| >| a
| >| >| saved file, hence the need for saving a file. If
| the
| >| >| result is different from the saved file, then
| overwrite
| >| >| the saved file and email it as an attachment.
| >| >|
| >| >| Thanks for all your help!
| >| >| Hafeez Esmail
| >| >|
| >| >| >-----Original Message-----
| >| >| >Hi Hafeez,
| >| >| >
| >| >| >How do you perform your search? It may depend on
| your
| >| >| search design; we can
| >| >| >even put the search result in e-mail's body section
| if
| >| >| there is no specific
| >| >| >format requirement.
| >| >| >
| >| >| >What does the string "Predef120" & strLastName
| stand
| >| for?
| >| >| Is it an object
| >| >| >name (form,report,etc) or a just string? Would you
| >| please
| >| >| specify the
| >| >| >question?
| >| >| >
| >| >| >
| >| >| >Sincerely,
| >| >| >
| >| >| >Alick Ye, MCSD
| >| >| >Product Support Services
| >| >| >Microsoft Corporation
| >| >| >Get Secure! - <
www.microsoft.com/security>
| >| >| >
| >| >| >This posting is provided "AS IS" with no
| warranties,
| >| and
| >| >| confers no rights.
| >| >| >
| >| >| >
| >| >| >
| >| >| >
| >| >| >
| >| >| >--------------------
| >| >| >| Content-Class: urn:content-classes:message
| >| >| >| From: "Hafeez Esmail" <
[email protected]>
| >| >| >| X-Tomcat-NG: microsoft.public.access.modulesdaovba
| >| >| >|
| >| >| >| I want to save a search in any format within
| access
| >| >| that
| >| >| >| would allow me to send an email with it being an
| >| >| >| attachment. I don't know how to save it.
| >| >| >|
| >| >| >| Please help
| >| >| >|
| >| >| >| .....
| >| >| >| Do While rstname.EOF = False
| >| >| >| strLastName = rstname!Last_Name
| >| >| >| If rstrec.BOF = False And rstrec.EOF =
| False
| >| >| Then
| >| >| >| rstrec.MoveFirst
| >| >| >| Do While rstrec.EOF = False And
| rstrec!
| >| >| >| Last_Name = strLastName
| >| >| >| rstrec.MoveNext
| >| >| >| Loop
| >| >| >| >>> DoCmd.Save acDefault, "Predef120" &
| >| strLastName
| >| >| >| End If
| >| >| >| rst.MoveNext
| >| >| >| Loop
| >| >| >|
| >| >| >| ">>>" indicates the line that's giving me problems
| >| >| >|
| >| >| >
| >| >| >.
| >| >| >
| >| >|
| >| >
| >| >.
| >| >
| >|
| >
| >.
| >
|
.