Here is the ADO code I use.
First, add a Macola ODBC System dsn in Windows by going to Control Panel, ODBC Connection, Add. Setting up the dsn from here will vary by the database engine vendor and the version, so you will have to refer to your documentation, but in general you are looking for a driver in the list named "Pervasive Software ODBC-32" if you are running Btrieve or you would use the MS-SQL driver if you are running MSSQL.
After you have added the dsn (in this example, I have created a dsn called "MacolaODBC"
, add this code to your VBA module.
'place in module header so you can use the macola ' connection in any function
Public Macola As New Connection
'place in module
Function OpenMacolaConnection()
On Error GoTo OpenMacolaConnection_err
'initialize the object
Set Macola = Nothing
'this value may vary according to the speed of your system
Macola.ConnectionTimeout = 120
Macola.Open "Provider=MSDASQL;DSN=MacolaODBC"
OpenMacolaConnection = True
Exit Function
'***********************
OpenMacolaConnection_err:
'***********************
MsgBox Str$(Err) + Error$
OpenMacolaConnection = False
End Function
Call the function in your code by using this line:
Call OpenMacolaConnection
Subsequent code can then use the open connection to do stuff. In the following example I use it to create a copy of the ARTYPFIL in an Access database:
Function PopARTypeFile() As Integer
Dim macARTYPFIL As New Recordset
Dim ARTYPFILtbl As New Recordset
On Error GoTo PopArTypeFile_Err
'macola cust cross ref file
Set macARTYPFIL = New ADODB.Recordset
sql = " SELECT *"
sql = sql + " FROM ARTYPFIL "
macARTYPFIL.Open sql,_ Macola,adOpenForwardOnly,adLockReadOnly, adCmdText
'local edi cust list
Set ARTYPFILtbl = New ADODB.Recordset
Call TablesSql("DELETE tblARTYPFIL.* FROM tblARTYPFIL"
ARTYPFILtbl.Open "tblARTYPFIL", TablesDB, adOpenDynamic,_ adLockPessimistic, adCmdTableDirect
With ARTYPFILtbl
Do
.AddNew
.Fields(0) = macARTYPFIL.Fields(0)
.Update
macARTYPFIL.MoveNext
Loop Until macARTYPFIL.EOF
End With
'RELEASE MAC CROSS REF FILE
Set macARTYPFIL = Nothing
Set ARTYPFILtbl = Nothing
PopARTypeFile = True
Exit Function
PopArTypeFile_Err:
MsgBox Str$(Err) + " " + Error$
PopARTypeFile = False
End Function
Hope that helps
