How to connect databases using VBA in Macola Screens?

[cparralesl]

Hi,

I wanna make some calculations during a user is booking invoices but I do not know how to open a database using VBA. I have seem some functions that Macola gives and can be useful but I do not kow how to open a database. I would like to know the sintax or something that can help me.

If there is any one can help me I really appreciate.

Thanks in Advance
:~/ Cesar humberto Parrales
Application Support
Home : (505)289-2484
Mobil: (505)885-6016

 

[lovalles]

If you have experience on Visual Basic, you can acces a database in different ways, you can us ADO, RDO, or anyother wat. My advice is to use ADO, is faster and easier.

if you want some code i would be happy to send it.
hope this help
lovalles
Macola System Administrator

 

[dgillz]

Lovalles,

I am working on another project and I would lov eot see some code connectiong to a macola database with ADO. Please email me.

Many thanks Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[cparralesl]

Hello lovalle,

I will appreciate if you send yours code to me at cparralesl@hotmail.com

Thanks inadvance [/u][/i][/b][/color]Cesar Humberto Parrales
Application Support
Home : (505)289-2484
Mobil: (505)885-6016[/u][/i][/b][/color]

 

[cparralesl]

Hello Lovalle,

I will appreciate if you send your code to me at cparralesl@hotmail.com

Thanks inadvance. [/u][/i][/b][/color]Cesar Humberto Parrales
Application Support
Home : (505)289-2484
Mobil: (505)885-6016[/u][/i][/b][/color]

 

[vbajock]

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&quot , 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&quot

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

 

[Guest_imported]

Actually, you can't use DAO to connect to Macola unless you don't need to write records; only ADO can write.

You can use the built-in Macola variables to connect to the same database as your logged on Macola without worrying about setting a DSN; much, much more flexible.