Size Limiation VBA / Access???

[DMS500Tech]

I have spent a long time figuring out how to write VBA code to build a table in Access that has a column for every phone feature we use. It is about 180 columns wide.

I have spent a long time writing the VBA code to search an individual phone record in a text file and extract the feature information to the correct column. I have tested it endlessly with a small file containing most every combination of phone types and features.

Now comes the heart ache, when I tried to run it on a full blown live (text) file that is about 108Mb and contains around 180,000 records it chokes.

At first I thougth it was the laptop that the code was running on so I moved it over to my server and it still chokes at about the same place.

The code runs just fine then after a few minutes pops up a Runtime error 3001.
The thing I am looking at is the file size. It seems to keep hanging at 2,097,152KB. After the code is reset and the database is compacted it shrinks down to about 18,952KB.

Does anyone have any idea what I am doing wrong or is there someway to compact the database on the fly.
The server has some 280Gb of free disk space and has around 2 gig of memory so I don't think it is hardware, the laptop was not near that big and hung in the same place. I have tried to stream line the code as much as possible. I have tried to make most of the text fields as small as possible. I did not think about something like this happening because up until now I used a similar code in Excel to do about the same thing just move over so many columns and start again when the 65,536 row limit was reached.

I would have attached the code but it is about 50 pages long and works fine with small files but I really need it to work on this big one.

Any suggestions anyone??????????

 

[PHV]

a table in Access that has a column for every phone feature we use
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GingerR]

2GB is the limit.
As PHV suggests, take a look at your design. 180 fields is a red flag to me....

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[DMS500Tech]

so let me ask this, is the 2Gb Limit the maximum size of the database or the maximum size of a table?
I cannot seem to find any specs on the limitations of Access. I was under the impression it was only limited by the size of the hard drive.

If I was to break it down into several smaller tables and modifed the script to make several passes do you think I would still have the runtime error? The database did compact down to less than 19Mb after the script was stopped.

 

[KenReay]

Hi

You say:

"VBA code to build a table in Access that has a column for every phone feature we use. It is about 180 columns wide.

I have spent a long time writing the VBA code to search an individual phone record in a text file and extract the feature information to the correct column"

Why write VBA code to build the table, there is a table design option?

Could you not have imported the text file using the single line DoCMD.TransferText?

If you are doing lots of string manipulation (ie left, mid, right etc etc), it is heavy on memory usage

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[DMS500Tech]

KenReay, I wrote the code to built the table because the first thing it does is delete the old table. Figured that would be the quickest way to kill 180,000 records. I have found to clean up the database there has to be a compact and repair done afterwards but that only takes a moment.

As far as importing, the data is not formated in any way and the records vary in length from 15 to 50 lines with most all of them being a little different from the next and about 25% of the features vary in length as much as 30 characters.

If there was a way that could be figured out to click on the file menu then get external data and then import I would do it in a heart beat.

 

[GingerR]

2GB is the max size of an MS Access db.
I have db's with hundreds of thousands of records and they run fine. Slow sometimes, but don't choke out or anything.

As for the 180-field thing: I think what we are all thinking about the 180 is that you have a variety of Phone Types and Features. We would expect to see something like this:

PhoneType PhoneFeature OtherInfo
A Green Button Blah
A Blue Button Blah2
B Long Thing Data2
B Hold Button


instead of

A Green Button Blah Blue Button Blah2 etc
B Long Thing Data2 Hold Button

Of course we have no idea what your table structure is, but just from your comments, I think this is what we are imagining, and we think it could be better. Don't know if this is causing your problem (file bloating too big), but just a shot.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[DMS500Tech]

GingerR, That is a thought I can seperate them by phone type (different tables) but there is only 3 types of phones, Residential, Analog Centrex, and Digital Centrex. The whole purpose of this project is to be able to dump all this data into Access and do 2 things 1st audit phone features, types, and so on and 2nd to use the data for migrations.

For example I may have 50,000 phones with Call Waiting. It is a 3 character code CWT no more no less and I need to be able to query that information and compare it to a data dump from say the billing application but I need all the features from that phone to make a match.
On the other hand I may also have 50,000 different phones with Call Forward. There again a straight forward 3 character code CFW but this code has options, is it active/ inactive, how many calls will it pass, is it customer contolled, is it fixed ringing, is it programable ringing, if it has been used or active what it the number it was forwarded to.

There is one column (field) for each feature. One phone may have 40 different features and the next only have a couple so I need to take the whole monster in one bite and sort it all out with querys from within Access.

 

[GingerR]

I'm not saying I know how to structure all of your stuff right now because obviously I don't know the whole picture; I was just explaining the reason for our references to structure, and that it could be one of the reasons for the problem.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[DMS500Tech]

I hope I did not come across badly, I really appreciate Tek-Tips and everyones suggestions and tips.

I spent several hours last night looking at the data structure and it will not be that hard to break it down into smaller chunks of data. Let me work on it for a bit and I will get back with everyone with the results.

Thanks again for everyones input.

 

[lameid]

Aside from table structure, the other question that jumps at me is why does the database grow so much?

Are you creating a lot of tables for temporary use and then deleting them?

I would expect the memory footprint to be big and possibly even a swap file. But I would not expect the Access .MDB file to grow.

Are you using a FE and BE?

If using a lot of temporary objects you might consider using temporary .mdb files to handle the data. Even dispose of them when you kill the object. That may help keep the bloat down.

Although, you will probably be greatful for any breaking down of data you do know.

 

[DMS500Tech]

Following is basically all of the code with the repeat of the search section removed for space.
I have no idea why it goes from a base of 480Kb to over 2 gig in less than 22,000 records and then compacts to less than 19Mb after the code is stopped.
Any wise programmers out there see what I might be doing wrong?

Sub PopulateTableFeatures()

'************************************************
Dim dbsSwitchFeatures As Database
Dim rstFeatures As Recordset
Dim LinesFromFile As String
Dim NextLine As String
Dim MyPos As Long
Dim strDASH As String
Dim lblBookmark

'************************************************
'************************************************
Set dbsSwitchFeatures = CurrentDb
Set rstFeatures = dbsSwitchFeatures.OpenRecordset("tblFeatures", dbOpenTable)

LinesFromFile = ""
strDASH = ""
'************************************************
'Get data from the file

Open "c:\temp\TestFile.txt" For Input As #1 'Open file

'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Do While Not EOF(1) 'Loop until end of file ' 1st loop

Line Input #1, NextLine 'Read line from file into Variable.

'************************************************
'Loop thru file looking for Features
'************************************************
' Search LEN 'First 4 characters of every Record - Fixed No Options

If Mid(NextLine, 1, 4) = "LEN:" Then
LinesFromFile = Mid(NextLine, 10, 16)
With rstFeatures
.AddNew
!Len = LinesFromFile
Debug.Print NextLine
Debug.Print LinesFromFile
.Update
.Bookmark = .LastModified
lblBookmark = .Bookmark
End With

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
With rstFeatures
Do While strDASH = "" 'Second Loop
Line Input #1, NextLine 'read next line from file
'************************************************
'************************************************
' Search TYPE 'Fixed
If Mid(NextLine, 1, 5) = "TYPE:" Then
LinesFromFile = Mid(NextLine, 6, 40)
.Bookmark = lblBookmark
.Edit
!PTYPE = LinesFromFile
.Update
End If
'************************************************
'************************************************
' Search HUNTMEMBER 'Fixed
MyPos = 0
If MyPos = 0 Then
MyPos = InStr(1, NextLine, "HUNT MEMEBER:", 1)
If MyPos > 0 Then
MyPos = MyPos + 12
LinesFromFile = Mid(NextLine, MyPos, 4)
.Bookmark = lblBookmark
.Edit
!HUNT_MEMBER = LinesFromFile
.Update
End If
End If
'************************************************
'************************************************

((REPEAT SOME 180 MORE TIME FOR OTHER FEATURES – EXACT SAME CODE AS 2 SAMPLES ABOVE "TYPE" AND "HUNTMEMBER"))



'************************************************
'************************************************
'************************************************
'End of Record Search "-----"

If Mid(NextLine, 1, 5) = "-----" Then
LinesFromFile = Mid(NextLine, 1, 4)
strDASH = LinesFromFile
End If
Loop '2nd loop
End With 'Features Search
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

'************************************************
'************************************************
'FROM THE FIRST SEARCH "LEN"
End If
'************************************************
'************************************************

LinesFromFile = ""
strDASH = ""
'************************************************
Loop '1st loop
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

'************************************************
rstFeatures.Close

End Sub

 

[MoLaker]

From what I can see in your code, you perform an Edit and Update about 180 times per record. Is that correct? If so, you are attempting to perform over 32 million record updates (180 columns or features times 180,000 records). I believe when Access updates a record it basically leaves the old record space occupied and writes the updated record in a new space. Compacting cleans out this trail left by the updates. If this is true, you may be able to resolve your problem by collecting your data for each column in variables then perform a single Edit/Update on that record and move on to the next record - instead of updating 180 times per record.

 

[DMS500Tech]

MoLaker, I had tried a single update in the begining and had some issues but you have given me an idea to try.

 

[lameid]

After all your if statements where you edit fields then you should update. Ideally your can detect whether the recordset has been edited. While I have not used it it looks like Editmode should do the trick.

If .EditMode = adEditInProgress Then
     .Update
End if

While I have my doubts it would have any effect, a hunch tells me removing Debug.Print lines may help. It is at least easier than removing 180 update methods.

 

[matrixindicator]

Access has a lot of possibilities but as a desktop database it has also his fysical limits.

[URL unfurl="true"]http://www.databasedev.co.uk/access_specifications.html[/URL]

 

[DMS500Tech]

MoLaker, it looks like you were correct. I had tried a mass update at first calling a function but was having a problem passing the variables so I reverted to the method above because I could get it to work. I did not think about doing an .addnew and .update at the end of each record search before the next search started.

Well last night I took the first 14 fields and tried it and it ran thru the 180,000 records in less than 2 minutes with a total file size of just under 37Mb. After the Code was finished running I compacted the database and it did not change one byte thus no garbage.

So in my not understanding how this record update from a text file thing worked I was leaving a ton of garbage out in limbo that Access did not know what to do with.

As soon as I can re-assemble my original code I will post segments so maybe the next person will not have to go thru the grief I have.

Thanks again for everyones insight

 

[BSman]

Seems to me that you need to normalize your data. I'm guessing that you have a list of phone numbers and want to identify the features enabled on each phone. To make a quick (but really flexible) database you could use three tables:

1. Features table: contain two columns, one describing the feature, and another that is an autonumber column (primary key, let's call it "FeatureID")

2. PhoneList table: at minimum contains each phone number (which would be the primary key, or you might even be better using an autonumber column as primary key). I would assume you'd also have basic data such as the name, address, etc.

3. PhoneFeatures table: This would contain either the phone number or the autonumber column in the PhoneList table, plus a FeatureID column. You should also make an autonumber primary key column for this table.

The PhoneFeatures table would only contain a record for every phone number (listed in the PhoneList table) that had a particular feature. So if you had 10 phones with all 180 features, there would be a total of 1800 records in the PhoneFeatures table for those phones. If you had 10 other phones that only had one feature each, there would only be a total of 10 records in the PhoneFeatures table for those phones.

The reason for using the autonumber columns is that they take less storage space and, as integers, require a lot less processing time. Your spplication would never show any of the ID columns to users, since selection of features, for example, would use combo boxes that would display the description of the feature, but would have a value of the ID.

When you want to find what phones have a particular feature, the search will be very quick and easy, since you just search on the PhoneFeatures table and for each match, look up the other data (name/address of user, for example) from the PhoneList table. It will run much faster and take less storage space.

Bob