Albert
12/19/2006 8:49:00 PM
Thank you Alok,
I appreciate you taking the time to help me analyze the situation.
Albert C
"Alok" wrote:
> Hi Albert,
> With the information provided I cannot help you. However, you may like to
> consider moving to Excel 2007 since it has 1 million rows. The launch is I
> think scheduled for early next year but test/trial versions are freely
> available for download/ordering on CD. This will let you remain within the
> confines of Excel.
> Alok
>
> "Albert" wrote:
>
> > Hi Alok,
> > Well its complicated, but the main purpose of the program I am currently
> > using was to keep perfect trak of every single move/sale made by the company
> > (retail: 700+ records per day). Even though I am not discarding the
> > possiblity, I'd feel more confortable not having to aggregate the central
> > data base. The problem is not so much working with the data base, but storing
> > it in a way that is confortable for my VBA code to use.
> > The reason I need all the records is because Merchandise is permanently
> > moved around our warehouses, and every process has to be assigned to a
> > specific location (the latest one).
> > So you see I am facing quite the conundrum.
> > Thanks,
> > Albert C
> >
> > "Alok" wrote:
> >
> > > Hi Albert,
> > > I just want to know if you definately need all the records in the central
> > > database for the daily processing? Maybe you just need some aggregate
> > > information from them (which will be good to know since one can then think of
> > > aggregate queries on the data stored in a database) or you just need some
> > > sub-set of records to validate the current record being entered. Can you
> > > provide details as to why you need all the records at the workstations for
> > > each day's work?
> > > Alok
> > >
> > > "Albert" wrote:
> > >
> > > > Hi Alok
> > > > What I mean by "storing in a Public Array" is that I open the central Data
> > > > Base workbook and I use this command:
> > > >
> > > > Public MyDBase() as Variant
> > > > Sub StorMyDataBase
> > > > MyDBase = Range("A2").CurrentRegion
> > > > End Sub
> > > >
> > > > It is a Data Base that inlcude all the sales the company has done this year.
> > > > It is needed to process the new sales information and the stock analysis
> > > > I've set up.
> > > > At the end of the day, I go into the central DataBase and import the new
> > > > records from the network stations.
> > > >
> > > > Thanks in advance,
> > > > Albert C
> > > >
> > > > "Alok" wrote:
> > > >
> > > > > Hi Albert,
> > > > > Please explain as to what you mean by 'Store it in a Public Array' also give
> > > > > some background of how and why you do it. Also how you update the central
> > > > > Excel workbook at the end of the day. With all this info someone may be able
> > > > > to suggest an alternative architecture.
> > > > > Alok
> > > > >
> > > > > "Albert" wrote:
> > > > >
> > > > > > Hi!
> > > > > > I´m in a complicated situation.
> > > > > > I have 4 computers on my network working on one database.
> > > > > > My setup is like this.
> > > > > > The network stations each has a workbook programmed with vba. Every day,
> > > > > > before doing any work, the station workbook opens the central database (excel
> > > > > > workbook) and stores all its data as a public array. The guys on the stations
> > > > > > do their work and at the end of the day, I import the records they generate
> > > > > > into the central data base.
> > > > > > So far, everything is going great: The speed is acceptable and the files
> > > > > > have managable sizes.
> > > > > > However, my central data base is about to reach 65000 records (rows). What
> > > > > > should I do?
> > > > > > If I put the central data base in Access and it grows over 65536 records,
> > > > > > will my Excel stations still be able to read it and store it as a Public
> > > > > > Array?
> > > > > > What else can I do?
> > > > > > I was thinking of turning the Central Database into a multi-sheet workbook,
> > > > > > but I'm concerned about the speed with wich the Database is opened and stored
> > > > > > as a public Array.
> > > > > > Any advice would be greatly appreciated.
> > > > > > Thanks in advance,
> > > > > > Albert C