Access Modules / Automation of Reporting Databases

[TimX416]

Hi -

I use access as a middle ground for all of my financial and operational reporting. Since I am so anal retentive, I always obtain data at the lowest level available from our production environment. I create macros that imports the text and then updates some created fields for analysis. I have tried creating a module that will delete a table then compact the database then transfer text into the table structure. However, I can't figure it out and am not as skilled in VBA for Access.

Therefore - 2 requests from my Access brethern:

- Can you give me a head start with a module such as that described above?
- Suggestiongs about additional training for a Finance Manager with limited need for creating production databases out of Access (e.g. don't want to be a programmer full time, but want to expand my capabilities in Access)?

Your thoughts, input and guidance are well appreciated.

TimX

 

[Ziggy]

You don't need to use VBA for what you want, what you do is create delete and append queries and control them in sequence with a series of Macro Actions.

eg

1. Import
2. Append Query
3. Delete Query
4. Compact

I don't think there is a Compact action in Macro's and I can't remember the VB for it.

What I usually do is set up the sequence and then convert the Macro to VB and then modify it there.

Ziggy

 

[mdmilner]

I have a similar recommendation, but I'm a Macro-Phobe. I prefer VBA, although, somewhat interestingly, the method I'm about to recommend (DoCmd.RunSQL) really is a Macro command.

Start this way: Build queries so that you can do the actions you wish to do. That is, if you click them in a specified sequence (each intended to move you one step towards the goal) everything works.

Then, open them up in Design View, and then right-click the blue bar at the top and select SQL View. Copy-n-paste what you see to a text editor (WORD?). Then paste them into the field value for strSQL below.

Public Function RunQueries()
Dim dbs = DAO.Database
Dim strSQL As String

Set dbs = CurrentDB()

 strSQL = "SELECT tbl1.fld1, tbl1.fld2, tbl1.fld3, tbl1.fld4 "
 strSQL = strSQL & "FROM tbl1"
 DoCmd.RunSQL strSQL

Set dbs = Nothing
End Function

The & character allows you to append new information to the string variable above. You can also use the _ character to continue on the next line. This would look like:

strSQL = "Hi, this is just a " _
             "sample of what it looks like"

The above method (DoCmd) works for Action queries only.

Now, back to the rest of your question. Compacting and Deleting tables.
http://www.mrexcel.com/board2/viewtopic.php?t=66860&highlight=compact+vba

At the end of that thread is a code snippet for compacting Access2K+ mdb's from code. Below is how you delete a Table - this deletes it, it does not delete all the entries in the table. acQuery works for queries, for example.
If all you need to do is empty all contents of the table, you might try using an Action Query ("DELETE * FROM tblName") using the method I quoted above (DoCmd.RunSQL strSQL)

DoCmd.DeleteObject acTable, "tablename_in_quotes"

The only problem I have with deleting a table from code is, this doesn't check to see that the table even exists first - and if it doesn't, it generates an error message that you would need to handle. Best technique is to check first. Here is a code segment from:

http://www.mvps.org/access/modules/mdl0014.htm

' Pass the Object type: Table, Query, Form, Report, Macro, or Module
' Pass the Object Name
Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
     Dim db As Database
     Dim tbl As TableDef
     Dim qry As QueryDef
     Dim i As Integer
     
     Set db = CurrentDb()
     ObjectExists = False
     
     If strObjectType = "Table" Then
          For Each tbl In db.TableDefs
               If tbl.NAME = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next tbl
     ElseIf strObjectType = "Query" Then
          For Each qry In db.QueryDefs
               If qry.NAME = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next qry
     ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
          For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
               If db.Containers(strObjectType & "s").Documents(i).NAME = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     ElseIf strObjectType = "Macro" Then
          For i = 0 To db.Containers("Scripts").Documents.Count - 1
               If db.Containers("Scripts").Documents(i).NAME = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     Else
          MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
     End If
     
End Function

Mike

 

[Ziggy]

Everybody always say's they want to delete a table but I think Tim wants to delete the records then compact to reset the autonumber and keep the DB size down?