Flexibility Script Transported Does Not Work in Certain Machines 1

[atlanticdit]

Hi All,

Using Flexibility in a MS SQL 2000 environment and Macola Progression version 7.6.300C. Have created a script to pop up a Message Box warning users of a company on credit hold. The script was transported through the VBA TransportScript Utility in Macola. I've done it this way before without any issues. The script works on my computer as well as on another computer that has Flexibility installed. This other computer is also used by another administrator like myself. The problem is on two other computers that are used by regular users. The script doesn't launch on these computers--even if I use my Macola account. I installed Flexibility on one of these computers but it still didn't work. I did notice that in the References, under Tools in the VBA module, there was a notation that Data Object Wizard was missing. Can't see where I can download that Reference anywhere and I've successfully transported other scripts to computers that didn't have Flexibility installed and that used these same references.

My question is if there is a certain module, DLL, data component that needs to be installed on these machines to get them to work? Or do I have to do something other than just use the TransportScript utility? The code for the script follows:

Private Sub AmountPaid_GotFocus()

    Dim connString As String
    Dim connARCUSFIL As New ADODB.Connection
    Dim rsARCUSFIL As New ADODB.Recordset
    Dim strCustomerNo As String
    Dim strCustNo As String
    
    
    connString = "Provider=SQLOLEDB.1;Persist Security Info=False;Data Source=;Initial Catalog = ;User ID=;password="
    
    With connARCUSFIL
        .ConnectionString = connString
        .Open
    End With
    
    strCustomerNo = "00000000" & Me.CustomerNo.Text
    strCustNo = Right(strCustomerNo, 12)
    
    
     
    rsARCUSFIL.Open "Select * FROM ARCUSFIL_SQL WHERE cus_no = '" & strCustNo & "' ", connARCUSFIL, , adLockReadOnly
    
   
    If rsARCUSFIL.Fields(23) = "Y" Then
        MsgBox "This account is on credit hold. Please check against your list to see if they should be off credit hold!"
        rsARCUSFIL.Close
    Else
        rsARCUSFIL.Close
    End If

End Sub

The user id and password in the code are not blank as they are here. They are our admin username and password for our SQL database. Also, the data source and database name are intentionally blank but are correct in the actual code.

Thanks,
Dave

 

[abaldwin]

Question.
1. When you sign into one of the non-machines using admin credentials (yourself) and then log into macola as either you or the normal user does the code run?

I believe that normal users in addition to needing rights to the ODBC keys (there is a white paper on Macola's site), also you can search here for the right keys, need full access.

Also I believe they need full access to the temp folder on the machine. Most non-admin users do not have rights to either the ODBC keys or the temp folders.

On a side note.

YOu can enhance the performance of yoru code a little. If you are interested, post back.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[atlanticdit]

Thanks Andy. I have logged in as myself to no avail. The users do have admin rights for their particular computer but not for the network.

I will look at that white paper you suggested.

 

[abaldwin]

Couple of things to try.

First

replace your connection string with
Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

On the username and password. Get them from the macform.conn.info properties.

This will insure that the user name and password are drawing from teh _SQL user that you know has rights. It is also bad practice in flex to hard code a user name and password.


Second thing to try
replace your open statement with this
rsarcusfile.open "SELECT * FROM arcusfil_sql where cus_no = '" & strcustno & "' ", conarcusfil,adopendynamic,adlockoptimistic



If you get this to work I would suggest the following code be used to replace this sub.

Private Sub AmountPaid_GotFocus()
    Dim connString As String
    Dim connARCUSFIL As New ADODB.Connection
    Dim rsARCUSFIL As New ADODB.Recordset
    Dim strCustomerNo As String
    Dim strCustNo As String
    dim strSql as string
    
    'Uses the macform.conninfo properties to get the user name and password.
    connARCUSFIL.open "Provider=sqloledb;Data Source=" & macform.conninfo.server & ";Initial Catalog=" & macform.conninfo.db & ";User Id=" & macform.conninfo.user & ";Password=" & macform.conninfo.pwd;"
       
     strcustno = macform.customerno.text
     do while len(strcustno) < 12
        strcustno = "0" & strcustno
     loop
     
     'Build the sql statement
     strSql ' " SELECT hold_fg FROM arcusfil_sql where cus_no = '" & strcustno & "' AND hold_fg = 'Y'"

     rsarcusfil.open strsql,connarcusfil,adOpenDynamic, adLockOptimistic

    with rsarcusfil
        if .eof and .bof then
            'customer not on hold  insert code here if you want to notify the user of this
        else
           MsgBox "This account is on credit hold. Please check against your list to see if they should be off credit hold!"
        end if
        .close
     end with
    
    set rsarcusfil = nothing
    connarcusfil.close
    set connarcusfil = nothing


End Sub

I typed the code above here in the forum not in an editor. You should be able to copy and paste but may have some typos.



Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[atlanticdit]

With a couple of small modifications, your code works fine. But it still doesn't work in the two non-admin machines. Thanks for the cleaner code.

Something else I just tried that produced results that may help us troubleshoot this better. I installed the Flexibility client on one of the non-admin machines. I went into the VBA form that we've been working on and checked the Tools | References. I saw that the Data Object Wizard was missing from their references. As I didn't believe this was a necessary reference, I removed it.

After that the code started working on this machine. So I removed the Flexibility client from this machine; went back to my computer and removed the Reference to Data Object Wizard on my form and then Transported the Script to the machines again. After doing this I went back to the non-admin machine and it didn't work again. Therefore, it seems like it has something to do with the Flexibility client. I have other Flexibility code I've done that works with computers that don't have the Flexibility client on it so I don't know why this one is not working when the Flexibility client is not installed. Any thoughts?

 

[MacolaHelp]

I'm not a programmer, but can different versions of mdac be an issue? I found this to be the case on an excel app with vba embedded at one site. Some machines worked and others didn't until I used the same version of mdac on all machines.

 

[abaldwin]

I would reinstall the SQL client on both machines, to insure the ADO components are installed and up to date.


BTW install the flex client on all machines running. I don't understand what you mean by other flex code running that works on computers that don't have the client installed.

Install the SQL client on all machines running Macola.

Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[atlanticdit]

Ok, Andy, I think that is it. I think that not having the Flex client installed is the issue. I thought other Flexibility code I had written, for lets say the Order Entry screen, was working but I just looked at it again and its not working on the machines either. I had tested it on certain machines and now that I think of it those machines had the Flex client installed because they were admin machines, so it worked on them. The other issue is that the users here don't tell me when something is not working so if I say they will get a MsgBox when a certain event happens and they don't get it when that event happens, they don't tell me. That's as much my fault as anyone.

Thanks for the help. We only bought 3 Flexibility licenses so I'll have to look into getting more licenses. We understood the Flex licensing as you were buying the licenses for those that would create Flex code but the actual users of the code didn't need a Flex license or Flex installed on their machines.

 

[dgillz]

Whoa--

They do not sell Flexibility by licenses, if you have it, every machine with the Macola client can have Flexibility on it.

The ability to create and/ormodify Flex code is a function of Macola security, not seat licenses.

If you paid for this you got taken.



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[abaldwin]

Someone else here like Don would be better suited to address your license issue but here are the steps to install Macola progression on all workstations

1. Install the DB client (in your case The sql client) in others the pervasive client. Install the Full sql client, not just the connectivity only. Some VARS preferr the connectivity only for some dumb reason but I find that when I go to troubleshoot machines it is nice to be able to login and have the tools ready.

2. Check connectivity from the client to the DB system.

3. Install the Progression client from your install directory.

3. Install the Flex client on all machines that need to run flex applications. (Talk to your VAR about license issues). As a screen with Flex requires Flex client to run.

4. Test progression logged into the network as the user and Progression as both the user and the supervisor. Test the flex apps (in your case, open a customer you know is on hold).

5. Allow end user to begin using the system.

6. Walk away and get a pint. You will need the pint because all networks and network applications are flawless until the end users become involved.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[atlanticdit]

I will be double checking with my partner in crime in the IT Dept here. She is out today but she is the one that made the license purchase and took the lead in getting Flexibility here. Somewhere down the line there was an obvious misunderstanding.

I went in and installed Flexibility client on one of the non-working machines and now the Flex script is working fine.

Thanks for all the help,
Dave

 

[MacolaHelp]

Flex is licensed as your macola is licensed: if you have a 15 user system, you buy a 15 user flex. Macola licensing is concurrent users, so 15 users can access order entry with a flex app under the hood simultaneously. It's up to you how many people do development.

Crystal was about the only thing you could buy individual licensing for. Through version 7, you could have single or 5 user packs of crystal, which allowed you to have crystal on many machines, but only 1 or 5 0r 10 using the report generator concurrently. As of 8.5 and above, any machine that has crystal installed must be licensed, regardless of concurrent use. Maybe that was causing the confusion with flex?

 

[NEmacGuy]

I would not put my self in the same league in flex or programming, but I have seen some inprovements in performance when a connection string is coded as read-only when no updates to the database are required. Not sure of the syntax, but interested in feedback.

 

[abaldwin]

I have not seen any noticeable performance in doing so. In this case I would like to see a way to benchmark it anyway.



Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.