Database Search

TechHome

• Access 2000 Advanced DML Transactions
• Access Recordset to CSV File UDF
• Access RunCommand Constants
• ACCESS Table or Query Exists
• Access to Excel via SQL
• Access Visible Problem
• Adding Fields to an Existing Access Table
• Advanced DML Transactions
• Advanced SQL in the Jet 4.0 Provider Example
• Another OLE with Access Example
• Automating Access Tutorial
• Check if Access Table Exists
• Commit Rollback Begin Trans Updatebatch
• Control Access Forms
• Count Access Records
• Csv to Access
• Dealing with ACCESS 97 Field Types
• execute a macro in Access
• Execute a Public function in Access
• Export Access Report in Choice of Formats
• Export MDB to Flat File
• Find Read and Insert Data in Access
• Gather ACCESS Table and Column Information Into Excel
• Get Access Version That Created MDB File
• Get Column Data From an Access Database
• Get Record Count
• How to Use the OpenTable method
• Launch Access Macro from Commandline
• Loop thru a recordset in Access
• Office 2003 Macro Security Issue
• OLE and Access
• OLE Import a table into Access
• OLE Import Table into Access
• Open Access DB with DAO
• Pack an Access Database
• Query and Export Data from Microsoft Access
• Read Write Delete Tables from Access DB
• Repair MDB
• Retrieve Data in Access
• SQL UPDATE Fails with Access
• UDF to Return Record Count
• Updating Table From Filtered Recordset
• Ways to Get Data from and Access file without Access installed

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

UDF to Return Record Set

Keywords: 	 return record set

; ======= Winbatch: UDF to Return Records in Database Tables ============= ;
;  1. This Example uses ACCESS, but should work with other Providers       ;
;  2. I use the SELECT COUNT instead of rowcount() as it will also         ;
;     work if an optional WHERE clause is sent as a parameter              ;
;                                                                          ;
;  Stan Littlefield  ( stanl@btitelecom.net )    July 1, 2001              ;
; ======================================================================== ;

#DefineFunction GetCount(cMDB,cat)
cConn  = cat.ActiveConnection
nt     = cat.Tables
nCnt   = nt.count()
If nCnt==0
   ObjectClose(cat)
   Return(-1)
Endif
aTables = ArrDimension(nCnt,3)
BoxOpen(cMDB,"Table ")
For i=0 To nCnt-1
   tbl = cat.Tables(i)
   aTables[i,0]   = tbl.Name
   aTables[i,1] = tbl.Type
   Boxtext(StrCat("Table ",aTables[i,0] ) )
Next

RS  = ObjectOpen("ADODB.Recordset")
For i=0 To ncnt-1
   If aTables[i,1] == "TABLE"
      cSQL = StrCat( "SELECT COUNT (*) FROM ",aTables[i,0] )
      RS.Open(cSQL,cConn,1,4)
      fld = RS.Fields(0)
      aTables[i,2] = fld.Value
      RS.Close()
   Endif
Next
BoxShut()
ObjectClose(RS)
ObjectClose(cat)
Return(aTables)
#EndFunction

; ==== Script Starts Here  =============
cMDB   = AskFileName("Select ACCESS Database",".\","MDB Files|*.mdb|","*.mdb",1)
If cMDB == ""
   Return
Endif
cat    = ObjectOpen("ADOX.Catalog")
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%cMDB%;"

aTables = GetCount(cMDB,cat)

If VarType(aTables) == 256
   For i=0 To ArrInfo(aTables,1) -1
      If aTables[i,1] == "TABLE"
         message( StrCat("Table: ",aTables[i,0]),Strcat("Records: ",aTables[i,2]) )
      Endif
   Next
Else
   message("Error in %cMDB%","No Tables in Database")
Endif
exit

Article ID:   W14925

File Created: 2001:11:08:12:41:00

Last Updated: 2001:11:08:12:41:00