Virtual Database

[cable]

I've got a problem in a access/excel (both 97) solution.

The problem needs to use more columns/fields (ie 255) than access or excel can cope with.

Now ADO 2.5 can (if i've read the right www pages) cope with a far greater number of fields.

I can't just use ado can i? as the 255 limit will also be in the access 97 program.

So can I create a virtual recordset? and also query that recordset?
Or would I have to 'create' a db via ado code?

 

[Mile-O]

I've got a problem in a access/excel (both 97) solution.

The problem needs to use more columns/fields (ie 255) than access or excel can cope with.

256 is the limit in 97, 2000, 2002, etc.

I'd venture that your problem is not Access/Excel 97 but your "solution". It sound like you have a design flaw.

 

[ColinEssex]

The problem needs to use more columns/fields (ie 255) than access or excel can cope with.

If you require more than 255 fields in one table in Access then you are not doing things correctly.
I suggest you search for "normalisation" of a database.

Col

 

[The_Doc_Man]

There are various ways to approach this problem. You should also be aware that another limit is likely to slam you in the face before you ever get to the 255 column limit. Maximum record size is also an issue for tables and queries.

First, I have to say that you cannot possibly have 255 columns in a single NORMALIZED table representing a real-world issue, though I could easily imagine a flat-file representation that would take up more room. More likely, you have something that takes up that many columns in Excel because there is something that we call a repeating group in the column headers. This claim of having too many columns is quite common to folks who are used to Excel but not Access, and therefore think in terms of flat files.

Second, if I'm wrong abut the flat-file mind set and you really DO have that many columns, there is still a way to deal with it, though the cost (in programming time) can become prohibitive.

Tell us a little about your problem. With extra detail, we might find that you have fewer columns than you think you have.

 

[Mile-O]

ADO 2.5 can cope with a far greater number of fields.

Also, ADO is a data access method; a way of accessing a recordset (table, query) and navigating it.

 

[cable]

ah ok, it could well be a design problem...though it wern't my design (and its not really a database either)

Ok its all to do with Notional Risk (what that is, not a clue).
It starts as a linked table (not sure where its created from) that has ~6k records, which can be broken down into a number of 'books', for each book we have a start amount, a start date, an end date and a rate.

for each record in this table the program then creates monthly information with 3 records per month (principal, maturing, accrual) and here's where the problem is, it does this for 5 years, and I'm supposed to make it 10 years.

Once this data is gotten, it then sums the rows together so the 6k records becomes around 60 odd.

Once its done that it then it swaps the data around a bit, so the 3 records per month becomes rows and we get one column per month, so we can now get 10 years of info onto this last excel sheet.

So the solution to the problem was to write directly to the last sheet, skipping all the 255 limits, and to do this I was using arrays (added advantage of easy writing to excel)

The reason for the question is the sum part, not easy to do in the loop, as I can't do it on the array but would like do a 'group by query'.

At the mo my current solution is gonna involve text files as I can implement this now, and it will work...I'm sure there are far better solutions.

 

[mission2java_78]

Re: Re: Virtual Database

256 is the limit in 97, 2000, 2002, etc.

I'd venture that your problem is not Access/Excel 97 but your "solution". It sound like you have a design flaw.

I don't think its a design flaw, look into SAP

 

[a.sinatra]

Memo Field?
________
DRUG TEST

 

[cable]

Memo Field?

no, no memo fields in this problem...though freakily there is in another problem i've got to solve