Collecting data from Access DB with VBA

[jonsan]

I am writing a script in VBA which will gather data from multiple Access tables and store them in a single recordset to be transferred to Outlook or Excel. I am using ADO objects.

How do I append more data to a recordset?

Thank you,
Jon

 

[SkipVought]

Hi,

Use CopyFromRecordset method.

You can determine the next row by...

with activesheet.usedrange
  cells(.row + .rows.count, "A").copyfromrecordset rst
end with

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[jonsan]

Can I add data to an existing recordset as I Select information from the different tables?

Jon

 

[SkipVought]

I thot you wanted this in Excel. Run it in Excel VBA.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[jonsan]

The information will be sent either to Excel or to Outlook (depening on what the user selects), but I would like to gather the data into a single recordset.

Jon

 

[SkipVought]

Isn't that a just a query?


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[jonsan]

It would be a simple query, except that I need to eliminate some of the data based on a value. I need to pull from three tables, and in each of these tables is Column A. I need to write code to evaluate the data in Column A, and then delete certain rows that have matching values in Column B, based on the value in Column A. For example:

Get data from Table 1. If there are duplicate values in Column B, look at the value of Column A for those duplicate rows. Delete the duplicate rows where Column A = 1, but keep the rows where Column A = 2.

The values in Column A will be different in each of the three tables, so I need to determine which rows to eliminate before combining the data into a single table. Right? Any of this making sense? The actual process is much more complicated than what I wrote above, and includes a prioritization of the data based on the various values in Column A.

Jon

 

[SkipVought]

Can't that be done with another query?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[jonsan]

Can complex conditional statements be made in a SQL query?

Here is a more detailed explanation of what I need to accomplish. There are two columns important to the query (A and B). If multiple records have the same value for A, then their B values are relevant. B ranges from 1 to 6.

If records have equal A values then
check the value of B
If there is a record with B value of 1 then check the other records with the same A value. If we find a record with B value of 2, then the record with B value of 1 is deleted.
If there is a record with B value of 3, 4, 5, or 6, then check the other records with the same A value. If we find one or more records with B value of 3-6, use the following rules:
If 3 and 4, then delete the record with B=3
If 4 and 5, then delete the record with B=5
If 6 and 3, then delete both records
If 4 and 6, then delete the record with B=4

This is a generalized and somewhat simplified version of what I need to do. Can this be done with a query?

Thanks,
Jon

 

[smurfyIAG]

Can we assume that by "delete the record" you are meaning you don't want the record returned to the resultset, not that you want the query to actually delete the record from the original Access database (which it can do if you want)?

"Can this be done with a query?"
Short answer - if I'm interpreting the requirement correctly - Yes.
"Can complex conditional statements be made in a SQL query?"
Yes

Long answer - it'd be a doozy with several nested CASE and complicated WHERE clauses and some hairy joins!

Far better (IMHO) to return a slightly limited resultset to Excel and do the complex evaluations and filtering using VBa. before saving/transferring the end result.


Sean Murphy
Call Centre Analyst
IAG New Zealand

 

[solidpoint]

Sean,

I am up against a deadline and very much would like to know how to delete records from an Access table from Excel-VBA. Some syntax would be greatly appreciated. I have spent the better part of 2 days trying to get this to work, and so far have only gotten "Automation error"s for my trouble. If you could help I'd like to give you a big TVMIA.

Roy

 

[JerryKlmns]

solidpoint,

You should have posted a new thread.
Any way
In order to deal with data in a database you need a connection object.
Generally

Dim App_Cnn As ADODB.Connection

Set App_Cnn = New ADODB.Connection
With App_Cnn
   .Provider = "Microsoft.Jet.OLEDB.4.0"
   .Properties("Data Source") = FullPathAndNameTo[b]MDB[/b]File
   .Properties("Jet OLEDB:System database") = FullPathAndNameTo[b]MDW[/b]File
   .Properties("User ID") = PowerUser
   .Properties("Password") = PowerPswrd
   .Properties("Persist Security Info") = True
   .Properties("Jet OLEDB:Database Password") = DBPswrd
   .Properties("Mode") = adModeShareDenyNone
   .Open
End With

If no level security delete properties #1,#2,#3,#4
If no database password delete property #5

Then create the SQL statement to delete some records from a table that match some criteria

Dim strSQL as String
strSQL="DELETE myTableName.* " & _
       "FROM myTableName " & _
       "WHERE myTextFieldName='bliah'"

[green]'or for numeric field data type[/green]
strSQL="DELETE myTableName.* " & _
       "FROM myTableName " & _
       "WHERE myNumericFieldName>35"

[green]'or the entire data in the table[/green]
strSQL="DELETE myTableName.* " & _
       "FROM myTableName"

then execute the action query that deletes those records

App_Cnn.Execute strSQL, myRecordsAffected, adCmdText + adExecuteNoRecords

myRecordsAffected is a variable of type Long holding the number of records that where deleted
adCmdText evaluates strSQL as an sql statement
adExecuteNoRecords intsructs not to return any records

When done, close connection

App_Cnn.Close

and destroy connection object

Set App_Cnn = Nothing