hi to all. I'm quite new to VBS, 1

[teach314]

hi to all.

I'm quite new to VBS, but I could use some help.

I have a table in an Access mdb like this...

X   Type
--------
11   234
14     7
22    98
23   118
36    47
etc...

I want to step through each X value then do an analysis based on the Type value. In VBA, I would just set up a recordset of X values, then step through it. But, each step involves a lot of work, so I am required to frequently do a Compact/Repair to keep the mdb's size < 2GB.

For this reason, I 'run' the Access code from VBS so that I can close the mdb file and do the compaction after every new X value.

If the X values were 1, 2, 3, 4..., I could just use a simple For-Next in the VBS code. Here's the question - what is a good way to feed the X values into the VBS code so that I can loop through them?

thanks for any clues
teach314

 

[SkipVought]

Hi,

Why not VBA, using ActiveX Data Object (ADO) rather than DAO? You’d have the same issues with VBS AFAIK.

So what would your process be, since, “each step involves a lot of work, so I am required to frequently do a Compact/Repair”?

Something I’ve done in the past, is to write a Function takes an argument like your x value, opens the db, performs a query to grab a value for the given x and close the db.

Then you can do your work. Just a thought.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[teach314]

hi Skip - you're last thought re a function that takes arg X to open the db, then perform a query sound great! Thanks

 

[teach314]

hi Skip - I'd like to follow up on your earlier response.

I'm quite unfamiliar with ADO, but am willing to learn if it will help.

Are you suggesting that if I run my code using ADO instead of DAO, that I can somehow periodically step out of the code to do a Compact???
Or, are you saying that I could use some ADO in an Access program to 'control' the main DAO program?

What I'm really after is a way to periodically stop my VBA program because of bloating problems. I was using the VBScript to close my main mdb file, run code in Compact the mdb, then reopen and continue to run the Access VBA. Hope this makes sense.

Thanks for any hints
teach314

 

[SkipVought]

What has your experience been? How often do you need to compact? What criteria?

The thing that I’m not sure of is if you’re in the VBA of your database, is your db able to be compacted? Could you simply run x number of X values or from X[sub]1[/sub] to X[sub]2[/sub]. Then do a compact?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[teach314]

hi Skip - yes, I could do x number of loops in VBA, then compact. But, the VBA code is part of a math research study and runs for about 30 hours on average. So that I can run it through the night, I use VBScript to open Access then stop it after a designated number of iterations. This takes about 30 minutes by which time the mdb has grown to about 1.5GB in size. The VBScript then runs code to Compact the MDB, then the whole cycle repeats.

I'm puzzled by the fact that using VBScript to run the VBA is only about 70% as fast as just running the VBA alone. That's why I was wondering if there could be a better way to run the VBA in stages other than by using VBScript.

Thanks in advance for any tips
teach314

 

[SkipVought]

My Windows laptop in in the shop. I had some representative ADO code. Here’s some I scraped up...

Dim Rs1 As New adodb.Recordset
Dim sConn As String, sSQL As String
Dim iCnt As Integer

sConn = "Driver={Microsoft Access Driver (*.mdb)};" & _
                     "Dbq=nwind.mdb;" & _
                     "DefaultDir=C:\program files\devstudio\vb;" & _
                     "Uid=Admin;Pwd=;"


sSQL = "SELECT * FROM Employees"

Rs1.Open sSQL, sConn, adOpenForwardOnly

With Rs1
   On Error Resume Next
   .MoveFirst

   If Err.Number <> 0 Then
      Do While Not .EOF
         ‘Do stuff with recordset if you selected x Xs to process
      LOOP

      Rs1.Close
      ‘Here’s where you could compact your db
      
   Else
      ‘Here’s an error
      Err.Message

   End If
End With

Set Rs1 = Nothing

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[teach314]

thanks, Skip
I'm away for a few days, but I'll give this a try when I'm back. It looks very hopeful!

Do you have any reason to think that running my code from an external ADO program would be faster than running the my code from VBS?
Thanks, teach314

 

[SkipVought]

Don’t know. Try running a comparative test.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]