Multiple user query issue. Snapshot help please.

[9tanstaafl9]

Help please, I'm in way over my head. My report runs fine, except when multiple users run it for the same job at the same time (which they need to do often during meetings for some reason). Then it "slows to a crawl and they don't know if it would even run if they waited long enough".

Using Excel 2003, connecting ODBC to visual foxpro tables.

Their IT guy, who doesn't have time to fix it, told me they needed to be sure that I don't use "dynamic data sets" but instead use "snapshots" and something about "cursors". (We are reading the data only and do NOT want to change the tables at all.) It only needs to update data when they press a button, so they don't need to stay attached to it in any way. I don't know much about ODBC or queries, I trial and errorred this earlier.

In case it matters, here's what I run (thanks to someone here atain) to change where the database path is, maybe there's something in here that I could change?:

Sub ChangeDatabasePathManually()
  'changes all queries in workbook
 
 Const strPath As String = "U:\MXB7\Potter"
  Dim qt As QueryTable
  Dim wks As Worksheet
 
  For Each wks In ActiveWorkbook.Worksheets
    For Each qt In wks.QueryTables
      With qt
 
        .Connection = Join$(Array( _
            "ODBC;DSN=Visual FoxPro Tables;UID=;;SourceDB=", _
            strPath, _
            ";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;" _
            ), vbNullString)
 
      End With
    Next qt
  Next wks
 
  Set qt = Nothing
  Set wks = Nothing
End Sub

I am new to VBA and queries. All our other reports are written in Crystal, which I understand much better. I did a great job (with everyone here's help) of figuring out how to write the macros, and connect to their data, but I really know nothing about queries other than what I cobbled together to make it give me the right numbers.

Any help or pointers to other pages would be appreciated. Sorry for rambling, I'm very sick at the moment. (Bad thing about working from home, you can't call in sick.)

Jennifer

 

[9tanstaafl9]

How do I re-post without offending?

Obviously my question/title was poorly phrased. I know I'm not supposed to cross-post, and i don't want to be a jerk. How long am I supposed to wait before trying again? Is there any way to kill this thread and start over? What is the proper way to handle this?

I've seen lots of threads saying what you are NOT supposed to do, but not one that says what you SHOULD do if you really screwed up and posted poorly.

Jennifer