×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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...

CODE

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

RE: hi to all. I'm quite new to VBS,

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: hi to all. I'm quite new to VBS,

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

RE: hi to all. I'm quite new to VBS,

(OP)
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

RE: hi to all. I'm quite new to VBS,

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 X1 to X2. Then do a compact?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: hi to all. I'm quite new to VBS,

(OP)
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

RE: hi to all. I'm quite new to VBS,

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

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: hi to all. I'm quite new to VBS,

(OP)
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

RE: hi to all. I'm quite new to VBS,

Don’t know. Try running a comparative test.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close