How to read tables in access from 4 gl

[JerryC]

I just want to query an access data base from 4 gl. Is there a simple way to do that? I just need to read one table and search for new records.

Thank you

 

[sphipp]

The simplest way to do it is through Excel and the method works for accessing things like Sage as well.

What you need to do is to create an Excel Spreadsheet and use the Data - Import External Data - Import Data option to get data from Access. The wizard will talk you through which tables and fields you want to import. Then, you save the Excel Spreadsheet off as a CSV file and import it into Progress.

This sounds very clunky, but what you can do is to save the SQL query generated by the Wizard as a text file. Then you can create a number of SQL Templates to query different tables. Once you've done that, you can set up a spreadsheet with an _auto_start macro so that the spreadsheet performs an SQL query from a set text file, imports the data then saves it off as a set CSV file. Then to run the query, you copy whichever SQL template you want to the set text file, open up the Excel Spreadsheet, let it do its business and close down, then copy the generated CSV file into another location for processing.

Once written, it is a lot easier than it sounds.

I've used it to get information from Sage Invoices, Products, Orders, Stock and to generate EDIs form Sage. I've also used it to take information from a bespoke Access database.

If you need code samples, let me know and I'll try to dig out some examples.

 

[sphipp]

In Office 2003:

Data - Import External data - new database Query
This opens up a wizard.
In the Databases tab, double-clisk on MS Access Database and choose the database to query.

Go through the wizard to select the records.

If you view the query using Microsoft Query, the last option on the wizard, you can save the query off as a .dqy file.

This is an ascii text file that you can edit or generate.

You can use an excel spreadsheet as a template with an autorun macro:


Sub _auto_run()
'
' _auto_run Macro
' Macro recorded 07/09/2007 by Simon Phipp
'
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\db\db1.mdb;DefaultDir=C:\db;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Table1.name, Table1.age, Table1.Sex" & Chr(13) & "" & Chr(10) & "FROM `C:\db\db1`.Table1 Table1" & Chr(13) & "" & Chr(10) & "WHERE (Table1.name='simon')" _
)
.Name = "table1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.SaveAs Filename:= "C:\db\table1.csv", FileFormat:=xlCSV, CreateBackup:=False
End Sub

I'll try and dig out the macro to load up a query.

 

[WillieG]

I don't know the detail how, but have you ever considered using COM objects that can be accessed directly from Progress ABL/4GL.
If it can work on MS Outlook & MS Excell then surely it can work for MS Access.

 

[jongpau]

I don't know the detail how, but have you ever considered using COM objects that can be accessed directly from Progress ABL/4GL.
If it can work on MS Outlook & MS Excell then surely it can work for MS Access.

Yes, it can definitely be done with COM objects, which allow you to "directly" (well, more or less) access the database from the 4GL by executing SQL statements through COM. If you want I can see if I can find some code samples somewhere that do this (I have used it to build an interface with SQL Server once).

 

[BAMeyer]

Post deleted. Started a new thread.

 

[JerryC]

Post deleted. Started a new thread.

Thanks - I am back to this access thing - if you had a simple example - that would be very helpful

thanks again

 

[JerryC]

Yes, it can definitely be done with COM objects, which allow you to "directly" (well, more or less) access the database from the 4GL by executing SQL statements through COM. If you want I can see if I can find some code samples somewhere that do this (I have used it to build an interface with SQL Server once).

Seems I am back on this - if you could find a sample - I would apreciate it

Thanks

 

[parul]

Try this.

-Parul.

(Note: coded directly, might have syntax errors).

DEFINE VARIABLE DBPath AS CHARACTER NO-UNDO.
DEFINE VARIABLE Access AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE Rs AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
/* database path */
DBPath = "C:\work\access\Northwind.mdb".

/* Create instance of Microsoft Access */
CREATE "Access.Application" Access.

/* Open database */
Access:OpenCurrentDatabase(DBPath,TRUE).

/* Get the table 'your table' in a recordset */
Rs = Access:CurrentDB:OpenRecordset("your table").

/* Go to the first record of the table Test" */
Rs:MoveFirst().

/* iterate*/
DO i = 0 TO Rs:FIELDS:COUNT:

message Rs:FIELDS(i):VALUE view-as alert-box.

/* Get the next record of the table */
Rs:MoveNext().
end.


/* Close the recordset */
Rs:CLOSE().

/* Close the database */
Access:closeCurrentDatabase().

/* Exit MS Access */
Access:QUIT(0).

/* Release COM-HANDLE */
RELEASE OBJECT Rs.
RELEASE OBJECT Access.