00485: Example code showing how to read and write to an MS Access Data Base from Visual PRO/5

Title:

Example code showing how to read and write to an MS Access Data Base from Visual PRO/5

Description:

Here’s an overview that should help you get up and running: 

Step 1–Create a User Data Source from the Windows ODBC Administrator. 

To do this, run the ODBC Administrator from the Control Panel under Windows. Select the User DSN tab, and click the Add button. We are creating a Data Source for an Access database, so select the ‘Microsoft Access Driver (*.mdb)’ driver and click Finish. This should bring up the ODBC Microsoft Access 97 Setup window. Type in an alias to the Data Source in the ‘Data Source Name’ text box. It is suggested not to use spaces–” customer97″ was used for this example. Then click the ‘Select’ database button which will allow you to browse for a valid Access database. Select the desired .mdb file. Click on the ‘OK’ button and you’re done. The User DSN listbox should now contain your new alias (customer97 for my example).

Step 2–Manipulating the External Database via Visual PRO/5 

a) Example of writing data into a fictitious customer database 
0005 CUST_NUM$=”00011″,FNAME$=”JOHN”,LNAME$=”DOE”,COMPANY$=”ABC” 
0010 SQLOPEN (1)”customer97″ 
0020 SQLPREP (1)”insert into customer values (?,?,?,?)” 
0030 DIM B$:SQLTMPL(1,IND=1) 
0050 SQLEXEC (1)CUST_NUM$,FNAME$,LNAME$,COMPANY$ 

b) Example of reading all records from fictitious database 
0010 SQLOPEN (1,MODE=”ExecDirect=1″)”customer97″ 
0020 SQLPREP (1)”select * from customer” 
0030 DIM B$:SQLTMPL(1) 
0040 PRINT FATTR(B$); rem just for information 
0050 SQLEXEC (1) 
0060 LET B$=SQLFETCH(1,ERR=0200) 
0080 PRINT B.cust_num$,” – “,B.fname$ 
0120 GOTO 0060 
0200 END 

Notes: 

1) Note that the second example used the ExecDirect mode. This mode is sometimes necessary when retrieving information from an Access Data Base. If this mode is not used, character fields will sometimes be loaded with garbage. 

2) When using the SQLTABLES function in Visual PRO/5, an Access database will return the system tables in addition to the ‘normal’ data tables. The system tables all start with MSys, and are listed below: 

MSysACEs 
MSysIMEXColumns 
MSysIMEXSpecs 
MSysModules 
MSysModules2 
MSysObjects 
MSysQueries 
MSysRelationships 

Last Modified: 12/24/1997 Product: Visual PRO/5 Operating System: Windows

BASIS structures five components of their technology into the BBx Generations.