My tables are too big for Access...

[gblack]

Reaching the limits of Access. I have a simple concept. What makes it tough is that the 4 tables I am using are between 1.2 and 1.5M records in size. So when I run code that loops through these records, Access blows up. I already had one mdb file get corrupted because of this.

I have a list of service tickets by date. All I want to do is create a record set which pulls the first service ticket. I tried creating a record set then looping though the gigantic table (linked to a SQL Server database) but after about 449,000 records access gives me that you pushed me too far error and everything becomes “invalid”.

Is there a way to do this just using SQL and not VBA. I think there might be, but I am running into grouping problems when using “Distinct”. Or is there a way to deal with the size limitations with VBA?

Any help would be fantastic.

Thanks,
Gary

 

[Banana]

I'd try running a query on server side, not client side, which may be what is happening here. When you say you use VBA to loop through the recordset, do you know for sure it's working with few rows at a time, not requesting the *entire* dataset across the wire?

To prevent this, either use a stored procedure and call it or make a SQL passthrough query. I'd try former first.

 

[gblack]

Nope...

No that's not the issue. First off I don't think i can run VBA on the server side... can I? Secondly if i knew what SQL code I could use to do this in one shot and not use VBA... I'd run the thing in SQL Query analyzer.... but I don't so that was kind of my question.... I imported those tables into access and it still bombs... the tables are just too big I guess.

So my question is: how do I loop though giant tables without bombing, or what is the SQL code to get the first record of each ticket, using ticket_Date?

 

[Banana]

No, you only use VBA to call a stored procedure to make it run on server's side.

Let's say you create a stored procedure in SQL server and call it foo.

You then make a SQL Pass through Query and type in this line:

CALL foo;

.

If you want it to return a result set, make sure it will do. (Check the properties window).
Enter the ODBC connection string as well in same window.

Then in VBA you would do something like this:

Private Sub bar()

Dim qdf as QueryDef
Dim rst as Recordset

Set qdf=Currentdb.QueryDefs("foo")

qdf.Execute

Set rst= qdf.OpenRecordset

'Do whatever you wanted to do with it; make it a recordset of a report or a form for viewing or something.

End Sub

You'll need to look up on the documentations on SQL Server if you need helping writing a stored procedure.

 

[rsmonkey]

gblack remember access has a max file size of 2gb so 1.2-1.5mb per table is nothing. & you havent really made clear what exactly you're trying to achieve you say you want to select the first ticket dat wel http://www.w3schools.com/sql/func_first.asp look their.

Oh and i've got some loops running through my asset database which has 27million+ records and i dont get the invalid error its obviously some inconsistency within your db or code. Best thing to do is to open the VBA panel run the code and up on the top tool bar their will be a bit of text saying [running] next to the module name. When you get the invalid does it flag abit of code? zip up your db and post it so we can have a look!