Programming VBA / VB for Macola 4

[Rob7]

I have been wondering about this for a while. Some of you other programmers have said that instead of using the Macola ojects for connection (ERS) you are using ADO. I like ADO but wonder about the connection. Since there are a number of ways to connect though ADO, (DSN-less, DSN and OLEDB with DSN tags) which one is the best way considering the nearly infinit ways that SQL can be configured for security?

Thanks,

Rob

 

[Rob7]

I do actually have another question that I just ran up on. In the ERS recordset, if I queried a table for something that did not exist, I got an empty value. For example:

Dim rstTest as New ERS Recordset
rstTest.Connection = <ConnString>
rstTest.Open &quot;SELECT item_no&quot; & _
&quot; FROM IMITMIDX_SQL&quot; & _
&quot; WHERE user_def_fld_1 = 'whatever'&quot;

If &quot;whatever&quot; is not in the user field, rstTest(0) will be empty (&quot;&quot. However, I have found that in the same situation with ADO, I get an error indicating that the recordset is either EOF, BOF or has been erased. Is this normal or am I doing something wrong or am I using the recordset property wrong?

 

[abaldwin]

Back to using the username and password.

Remember that no matter what connection method you are using there is going to be a need in SQL to pass the user name and the password.

Using Flex you can get the current user name and password of the signed in user using the macform.conninfo.user and macform.conninfo.pwd

This way you do not have to expose any predefined username and password to the flex that most user can see if they really want to anyway.

Andy

 

[Rob7]

But isn't the ConnInfo available only when you are using the macForm? Can you access this through a VB exe that gets added as a menu item on the Macola Explorer?

ROb

 

[abaldwin]

You could do a shell to call your vb executable and send as the command line parameters any of the conninfo information.

Andy Andy Baldwin

 

[Rob7]

Hmmm. A very interesting thought. It so happens that most of the time, I add my executable as a menu item to Macola so I have to set up the DSN's to go to a particular data base. But I guess I could fire the exe off from a piece of VBA code.

 

[abaldwin]

This is especially useful in a multi company environment. No need to modify DSN's when companies change. Simply let the conninfo do the work and the vb executable parses the parameter to know the connection information.

andy Andy Baldwin

 

[li71]

How can I make executeable report script using ERS 5.0, I don't want my user change the source code.

Thanks

 

[dgillz]

This needs to be on a separate thread.

Typically you handle this by taking the flex icon away from the casual user and only your developers can have access to the flex icon and change the VBA code. You do this in System manager, maintain, visual menu builder. Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[vbajock]

Abaldwin, I tried the macform.conninfo.user property as you outlined and it came up blank. All the other properties, DB, btrvpath, etc are blank as well. I am working on a PSQL 2000 installation. Am I missing a Set statement somewhere or does this only work on SQL Server?

 

[abaldwin]

No...works fine in btrieve and sql.

Here is my connect string used in flex projects. This code makes my connect string work in both btrieve and sql without having to modify my code.

If macForm.ConnInfo.BtrPath = &quot;&quot; Then
'Assume Sql (Btrieve path isblank)
' Create a DSN-less connection to the Macola database.
rsRel.ConnectString = &quot;DRIVER={SQL Server};&quot; & _
&quot;SERVER=&quot; & macForm.ConnInfo.Server & &quot;;UID=&quot; & macForm.ConnInfo.User & &quot;;PWD=&quot; & macForm.ConnInfo.pwd & &quot;;&quot; & _
&quot;DATABASE=&quot; & macForm.ConnInfo.DB & &quot;;&quot;
Else
'Assume Btrieve
'Create a DSN-less connection to the Macola Database.
rsRel.ConnectString = &quot;DRIVER={Macola 32-Bit Btrieve (*.dta)};&quot; & _
&quot;DFE=DTA;AS=4096;Database=&quot; & macForm.ConnInfo.BtrPath
End If


Holler after you try this.

Andy Baldwin

 

[vbajock]

I'm not even getting that far.
Basically what I did is just plug in a command button on the macola ARCUSMNT form and put in one line of code:

Msgbox macform.conninfo.User

It and any other conninfo property comes up blank.

I am puzzled.

 

[Rob7]

Silly question from me vbajock, but did you put the reference to the ERSRECSET under tools?

Rob

 

[vbajock]

I have a reference to ERS 5.0 Object library. I don't see ERSRECSET in my object browser. I don't normally use the ERS record retrieval stuff - I am pretty much 100% ADO

 

[Rob7]

You're right. My bad. The reference is to the ERS 5 Object Library. I was thinking about the actual ERSRecSet object. I did exactly what you did, put a push button on a screen with just the properties returning to a message box and everything came back with information.

Rob

 

[abaldwin]

vbajock,
I had this happen only one time to me. All the conninfo was returning blank (actually null). I can not remember off hand what the solve was but vaguely remember a version problem.

What version of Progression, Flex, and Database?

Also try to re-install the flex client to your machine. The code above is like my personally most used code. When I write flex that accesses the database at all I use the above connect string. This way when the customer changes from BTRIEVE to MS-SQL my flex still works.

Andy

Andy Baldwin

 

[vbajock]

VBA 6, don't know how to check the flex version

PSQL 2000i

Mac 7.6.100a

vbconn.exe ver 76.0.0.2 date 12/3/2001

I am not setting up any type of connection prior to attempting to retrieve the macform conn info.

 

[Rob7]

What about the MDAC version? This is probably a low percentage check, but you might download the latest MDAC version from the Microsoft website. Really is a shot in the dark, but I haven't ever seen this problem before.

Rob

 

[abaldwin]

Are you writing this in Flex or VB?
Andy

Andy Baldwin

 

[vbajock]

I am using the Flex IDE in Macola.
MDAC is 2.6, latest ver
Client re-install didn't help.

I plugged in pretty much all of your code, and I can connect, open ERS recordsets, etc. I ran through all the conninfo properties and the only one I can retrieve is the btrieve path M:\macola70\data.

Tried it on another machine, same results

Rob7, when you said you did everything I did, are you on PSQl or MSSQL?