Need query help. EASY question for someone with experience...

[9tanstaafl9]

Hi! I'm using Excel 2003 and querying Visual FoxPro tables. My workbook has a summary sheet in front, and multiple tabs each with their own query providing the data used for my macro to create the summary sheet. Everything works great unless there are multiple users accessing the same information at the same time and then it slows to a crawl.

I know nothing about queries other than what I did to get the data I wanted. I need to copy the data, not edit the original tables. I've been asked to make sure that I am not connecting dynamically and that I'm just looking at the information when the button is pressed, not maintaining an open connection (I don't understand this and it wasn't covered in my vba for dummies manual...) They also said something about cursors being good...

My connection string is:

ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=c:\MB7\Sample Company;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;

I have background refresh set to NO.

The command text in the query editor shows:

SELECT bdglin.recnum, bdglin.cstcde, bdglin.hrsbdg, bdglin.matbdg, bdglin.labbdg, bdglin.eqpbdg, bdglin.subbdg, bdglin.othbdg, bdglin.usrcs6, bdglin.usrcs7, bdglin.usrcs8, bdglin.usrcs9, bdglin.ttlbdg, bdglin.phsnum
FROM bdglin bdglin
WHERE (bdglin.recnum=?)
ORDER BY bdglin.recnum, bdglin.phsnum, bdglin.cstcde

I really need help, and I apologize for posting this again but I've had no responses to my original post and it has been days.

Thanks!
Jennifer

 

[xenou]

I don't use MSQuery much and I've never used Foxpro so I'm not very qualified to help you out!

You didn't state exactly what you need help with but it sounds like you are concerned about the query slowing to a crawl when there are many users in the database.

To the best of my knowledge, the fact that you are using a Select query means that you will not be making any changes to the database so you can relax on that point. As far as dynamic versus snapshot datasets, some queries (even select queries) can return "live" data that will allow changes you make in the dataset to propagate back to the database. To be honest, I don't think MSQuery does this, though. The data that's returned to Excel is I believe static. It's usually more of a problem with Access tables, queries, and forms.

As far as the slow speed, I think that that's probably a function of variables such as number of concurrent users, nature of queries being run, capacity of the database, speed of network, number of requests/connections, and so on. Your query seems to return only one record -- WHERE (bdglin.recnum=?) -- what are these "multiple tabs" you are referring to and are you creating scores of database requests simultaneously?

I use MSQuery in a few situations to connect to SQL server and the output is just about instantaneous - so in general, I'm not finding it easy to put a finger on your problem but there are many possible causes here.

Sorry - not much help I guess. Alex.

EDIT: forgot to mention, I also don't think that MSQuery maintains an open connection once its returned data to the Excel sheet, and I couldn't find any way to make this happen either - so it looks like the default is to only connect when refreshing the data. As far as cursors go, I don't think its an issue in this situation. Your data is static, which amounts to a read-only cursor type.

 

[9tanstaafl9]

Your query seems to return only one record -- WHERE (bdglin.recnum=?) -- what are these "multiple tabs" you are referring to and are you creating scores of database requests simultaneously?

If what you know isn't knowing much than I am in even more throuble than I thought, because you sound like you know a lot. At least now I know that I was on totally the wrong track for why it was taking so long. There are about 20 tabs and each has its own query, and I picked the shortest one for an example. The other pages could return thousands of rows with about 10 columns each.

This one is more representative:

SELECT jobcst.recnum, jobcst.wrkord, jobcst.jobnum, jobcst.trnnum, jobcst.dscrpt, jobcst.trndte, jobcst.entdte, jobcst.actprd, jobcst.srcnum, jobcst.status, jobcst.bllsts, jobcst.phsnum, jobcst.cstcde, jobcst.csttyp, jobcst.vndnum, jobcst.eqpnum, jobcst.empnum, jobcst.payrec, jobcst.paytyp, jobcst.csthrs, jobcst.cstamt, jobcst.blgqty, jobcst.blgamt, jobcst.pieces, jobcst.lgrrec, jobcst.blgunt, jobcst.eqptyp, jobcst.eqpunt, jobcst.eqpqty, jobcst.grswge, jobcst.ovrrde, jobcst.blgttl, jobcst.active, jobcst.acrinv, jobcst.shwamt, jobcst.ovhamt, jobcst.pftamt, jobcst.taxabl, jobcst.usrnme, jobcst.ntetxt
FROM jobcst jobcst
WHERE (jobcst.jobnum=?) AND (jobcst.status=$1) AND (jobcst.actprd<=?)
ORDER BY jobcst.cstcde

Thanks for any suggestions. When users press the button that runs the macro, I can see the "ReadingData 0" line in the information bar for about 8 seconds. So during those 8 seconds what happens to the 2-10th user who runs my macro connecting to the same job?

Thanks!
Jennifer

 

[mole999]

Thanks for any suggestions. When users press the button that runs the macro, I can see the "ReadingData 0" line in the information bar for about 8 seconds. So during those 8 seconds what happens to the 2-10th user who runs my macro connecting to the same job?

Thanks!
Jennifer

If they are in the same excel, where the data is returned to they will see the same data.

If they are in other sheets, then the query maybe queued or fails

via SQL from a remote database I get circa 5000 staff postings in 10 fields in circa 2 seconds

 

[xenou]

Are you all using a shared workbook? Just curious - are you all in the same office or connecting into a shared network from various "home offices" etc.? How often and how likely is it that 2 users will run your macro at one time?

Or is it rather that you've placed the same code in various workbooks, each person using their own workbook but running the same macros.

Databases are *supposed* to be able to handle multiple requests but a lot depends on the robustness of the system - in general this shouldn't present a problem. Looking at your query - even if it returned 10,000 rows it should still be pretty fast (1 second maybe) - but I suspect the duration is lengthened by using an ODBC interface, and (even more) by a slow network.

I'm assuming you've recorded this code with MSQuery - is that right? Are these queries all run "as needed" one at a time when you are working on a tab? How often do you have to refresh the data?

Alex

 

[9tanstaafl9]

Are you all using a shared workbook? Or is it rather that you've placed the same code in various workbooks, each person using their own workbook but running the same macros.

I'm assuming you've recorded this code with MSQuery - is that right? Are these queries all run "as needed" one at a time when you are working on a tab? How often do you have to refresh the data?

Alex

Not shared. Each person has their own copy of the workbook on their laptop and connect to the data wirelessly over the server. They are all in the same conference room. God only knows why they don't just get a projector and have everyone look at it that way. There are about 10 of them, and they do all look at the same job at the same time.

I'm just hearing about this third-hand. I didn't know that was how they intended to use the workbook. If I'm not holding open any sort of constant connection (sorry I don't understand query-speak) then I'm thinking I'm not the problem, but that they'd have a problem with ANY workbook they tried this scenario under?

The code was recorded with MSquery. I turned off the auto refresh and put the following code (which I got here thankyou) in my macro to refresh each sheet (1 query each) before the macro does all the other things it does. I don't let them refresh manually since I've protected the workbook to keep the project managers from screwing things up.

Sub RefreshAllQueries()
   Dim wks As Worksheet
   Dim qt As QueryTable
   For Each wks In ActiveWorkbook.Worksheets
       For Each qt In wks.QueryTables
           qt.Refresh False
       Next qt
   Next wks
End Sub

Note that I HAD to put this in. When I tried to refresh all in the macro, the refresh wouldn't finish before other parts of my macro ran and I had all sorts of problems.

Thanks for your help. If I haven't done anything blatantly wrong let me know and I'll tell them they need to figure out some other way to do their reviews. I just wanted to be sure the problem wasn't me. Since I don't know much about queries, I accepted blame I maybe shouldn't have. Hopefully.

Thanks again for helping me!
Jennifer

 

[xenou]

I guess it is a bit of a funny problem. Sort of like when the CEO sends out an email to the entire company saying "hey look at our new website we just launched..." Then 5000 employees hit the site simultaneously and the server crashes.

I'm guessing the #1 issue is that since the macro refreshes all the query tables in turn, it's a heavy task.


One solution might be to put a refresh code into a sheet activate event with a marker. Result is:

1) Query table is only refreshed when a user clicks on a tab, which is less likely to occur simultaneously, and since its only one query will be much faster.

2) Query table will only refresh once per Excel session (while the workbook remains open).

Example (I've never actually used this before but it seems to work with a simple test - I'm using an array with room for 100 sheets as a way to track to refreshes...there's a small hook that if folks are moving sheets around the markers get off kilter so its not a perfect solution) -- or, protect the workbook structure to prevent moving sheets or adding sheets. Or, add a routine to reset the array to enable all refreshes again...(as below)

This Workbook Code:

Private a(0 To 100) As Boolean
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If Sh.Index <= 100 Then
    If Not a(Sh.Index) Then
        a(Sh.Index) = True
        Dim qt As QueryTable
            For Each qt In Sh.QueryTables
            qt.Refresh False
        Next qt
    End If
End If

End Sub
Public Sub Enable_Refresh_For_All_Sheets()
Dim i As Integer
For i = 0 To 100
    a(i) = False
Next i
End Sub

Edit:

Duh - I think you mentioned that all the queries feed a summary so actually you would need to refresh them all....

Well, out of ideas I guess. I'd be interested how this all turns out...it does seem like somehow we need to get the 10 users sharing the same workbook somehow.

Alex.

 

[9tanstaafl9]

Edit:

Duh - I think you mentioned that all the queries feed a summary so actually you would need to refresh them all....

Well, out of ideas I guess. I'd be interested how this all turns out...it does seem like somehow we need to get the 10 users sharing the same workbook somehow.

Alex.

There's a blizzard here, and an actual snow drift on my doorstep (which is under cover and a flight up. In Seattle they can't handle half an inch of snow (I think there are 17 snowplows for the entire county). The airports are out of de-icer, and my Christmas gifts for my kids are stuck in Portland... Anyway, when people get back to work I will figure out what's really going on and I'll let you know how this is resolved. Thanks for the other info, I'm sure it will come in handy in the future for other projects, assuming I don't go nuts from VBA weirdness and quit.

(I just spent 2 hours trying to figure out why my newly finished workbook crashed during testing, but only every 5th time or so. Finally figured out I had a button I wasn't using on a hidden worksheet that was getting pushed off the page when I inserted columns... arghhhhh!)

Thanks for the help! Jen

 

[xenou]

No problem Best wishes to you and yours. Alex.