Created on 2008-12-02 23:55:00
Posted by James Williams, Audio Precision Senior Applications Engineer
We often get requests for information on how to connect to a database in the AP Basic language. There are many options, and each requires some overhead. The simple script below is unique in that it does not require any external resources other than those provided by the standard Windows XP installation and an MS Access data file.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' AP BASIC DATABASE CONNECTIVITY EXAMPLE
' Audio Precision Tech Support
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Macro Description:
' This macro shows how to use the Microsoft DAO (Data Access Objects)
' collection to connect AP Basic to an MS Access Dbase
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Main
Dim DBEngine As Object
Dim DB As Object
Dim RS As Object
Dim FreqArray As Variant
Dim AmplArray As Variant
'--- These commands load the activex DAO object ------
Set DBEngine = CreateObject("DAO.DBEngine.35")
DBEngine.SystemDB = "C:WindowsSystemsystem.mdw"
DBEngine.DefaultUser = "NewUser"
DBEngine.DefaultPassword = ""
DBEngine.CreateWorkspace("JetWorkspace", "admin", "",2)
'Make MS Access Connection
Set DB = DBEngine.OpenDatabase("C:Your_FolderYour_dbase.mdb")
Set RS = DB.OpenRecordSet("SweepData")
'--- Input your test conditions (pruduct, serial no. etc.)
Product = InputBox("Enter Product")
SerialNo = InputBox("Enter Serial Number")
AP.Sweep.Start
Length = AP.Data.ColSize(0,0) '<--- returns the number of rows in the sweep
FreqArray = AP.Data.XferToArray(0, 0, "Hz") '<--- Your column parameters
AmplArray = AP.Data.XferToArray(0, 1, "%") '<--- Units must match instrument state.
For i = 0 To length -1 ‘ß- iterate rows mapping sweep data columns to fields
RS.AddNew
RS!Product = Product
RS!SerialNo = SerialNo
RS!SweepIndex = i
RS!Column1 = FreqArray(i)
RS!Column2 = AmplArray(i)
RS.Update
Next i
RS.Close
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
AP Basic can also connect to any ODBC registered data source; see the Microsoft documentation for ODBC connectivity.
Here’s an example for connecting to MySQL through ODBC
Note: This code snippet should be considered an example only.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
‘----set up a remote data connection using the MySQL ODBC driver. Change the connect string with your username,password, server name and the database you wish to connect to.
Dim cnMySql As New rdoConnection
Dim rdoQry As New rdoQuery
Dim rdoRS As rdoResultset
‘----set up a remote data connection using the MySQL ODBC driver. Change the connect string with your username, password, server name and the database you wish to connect to.
cnMySql.CursorDriver = rdUseOdbc
cnMySql.Connect = "uid=YourUserName;pwd=YourPassword;
server=YourServerName;" & _ "driver={MySQL ODBC 3.51 Driver};
database=YourDataBase;dsn=;"
cnMySql.EstablishConnection
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Once the sweep data records are stored, a wide variety of statistical tools can be use, such as:
- Probability plots
- Reliability tests
- Process Optimization
For example, standard deviation plots can indicate production run quality:
SNR for 100 units