worried about access maximum size and complexity of vba

[shutzy]

it has been some time since i was last on here due to the title of my thread. i have been researching different avenues and programs that will suit my needs.

i have a number of questions:
1. how realistic is it that i will use up all of the 2gb size with tbl, quries & forms.
2. what are the programming limitations of access with vba
3. can i use another programming format ie. sql with access to design my frontend forms.

i know that these questions can be non exhaustive and i am not looking for a complete answer. i suppose that without a detailed explantion of what i am wanting to achieve question 1 cannot be answered. i was just looking for a round figure(if that simple) of say a million rows of data.

i hope you can understand that i am having quite a dilemma of what to do. i am not a programmer but i am trying to build a database for my salon. the database will be used by many different users although one at a time as it will be at reception.

sorry but if i keep babling on i will just confuse myself and the thread.

if the answers to my questions are what i am looking for apart from the using a different programming language then how easy is vba to learn, is there any tutorials.

thanks for your help.

 

[mdlueck]

1. how realistic is it that i will use up all of the 2gb size with tbl, quries & forms.

Depends on what you store in the database. If you are using the DB to hold email with many emails having large files attached to them, then you will fill it quickly.

3. can i use another programming format ie. sql with access to design my frontend forms.

Yes, I am currently working on a Client/Server application where SQL Server is the backend. No data is retained in the Access front-end. In fact, the entire front-end database file is refreshed each time the application is started via the desktop shortcut.

You mentioned "for your salon business". I would suggest not to reinvent the wheel. Concentrate on your business, go find a pre-made solution OTHER than any product from Quickbooks! Bookwork / accounting programs are in my mind "dime a dozen"... many choices available. Surly your business is not so customized that something available will not be a decent fit.

 

[shutzy]

sorry its not an accounting program that i am trying to create. its basically an epos system that stores everything from client details to treatments, takings etc. and above all a reception appointment book.

we do have seperate software that handles the accounts which is fantastically basic but works.

as far as me mentioning the language. i mean the forms in access are helped along with vba. vba to me and my partners dad is more old school than he is. he used to be a programmer and is vey familiar with sql and i wanted to be able to pick his brains. if i have to use vba code then i wont be able to do that other than table structure and queries.

also, where about in the forum can i find out about creating receipts(sales, orders) whatever the terminology maybe.

basically i want a epos system with the added forms of a diary etc.

 

[CBrighton]

I would assume your reciepts would be reports, they are the most printer / email friendly ways of presenting data on a transaction imo.

I regularly use reports for things like invoices as well as normal reporting purposes.


Unless you are trying to do complex things you can get by with no VBA whatsoever. Macros and wizards can do most common functions for you.

As for database size, I have databases with hundreds of thousands of records which are still fairly small, it depends on how much data is in each record (or the data types of the fields in those records). I'm not aware of any way to judge the expected usage in advance. Regardless, none of my databases, including ones used by hundreds of people at once, have come even remotely close so far.

Given your usage (presumably customer & appointment data, possibly with billing, locally stored database saved on the reception PC) I expect an access database will be fine for you.

 

[mdlueck]

vba to me and my partners dad is more old school than he is. he used to be a programmer and is vey familiar with sql and i wanted to be able to pick his brains. if i have to use vba code then i wont be able to do that other than table structure and queries.

Yes, SQL is the way to talk to the database.

VBA is the programming language (in Access) which will drive SQL transactions against the database.

One can not write an application entirely in SQL. SQL needs a bit of help. In Access's case that is VBA.

basically i want a epos system with the added forms of a diary etc.

epos / accounting / eieio... I think you should still be able to find a prebuilt application for "salon management".

 

[shutzy]

now we have established that access will be adequate for me in terms of capacity i shall try and explain the main purpose of the database is.

at our reception pc we have (at the minute) a database that handles what i want to create. these databases cost 1000's and we need one for our other premesis.

i think i know what i need to complete the core of the database

from start to finish this is the core

customer books in a service(the service will be displayed on a diary view)
the customer arrives to have the service
the customers' service is complete and ready to pay
we receive payment and process the transaction

now this is a simple way of explaining what i want to achieve and all other aspects of this database will derive from the core.

i have danced around this for sometime and now i really could do with nailing it.

so first i need a diary can anybody help with that.

 

[CBrighton]

You don't start with data processing, you start with data storage.

Before you can decide how to manipulate the data you need to know what tables you will have and what fields they will contain. Without that how can you plan how the data will be edited when a customer arrives for their appointment?

I expect for the main this will be something like:

tblCustomerData
tblAppointments

In addition I expect various lookup tables such as a table of "products" (haircuts and anything else offered) and the standard price, a table detailing any standard discounts (xx% off for OAP's, etc), etc. The lookup tables are less vital to get nailed down early as they are primarily added for convenience.

 

[shutzy]

sorry i didnt want to make the post crazyly long

i have tables already inplace with forms and queries.

i have:
tblClientDetails
tblCategories(categories for different services)
tblTreatments(the services we offer)
tblTreatmentTime(appointment time/ to be used as a lookup)
tblClientAppointments

then i have forms:
main screen(to navigate to different aspects)
create new client
client search
create new appointment
client record card(client details form)

i have all the neccessary aspects done that i said would derive from the core. they have derived from the core but only in my head. thats what i mean by dancing around this. i have done as much as i can to avoid the diary etc but now it is like a road block and i need to get through it. then hopefully i will be cruising.

i do have many more forms but they are again extensions of the forms mentioned above.

as far as tables are concerned im not sure if tblClientAppointments is adequate or if i need more fileds. i wanting to know if a filed that hold the status value is appropriate.

ie. 1=default 2=arrived 3=paid 4=cancellation and so on.

 

[gemma-the-husky]

now we have established that access will be adequate for me in terms of capacity i shall try and explain the main purpose of the database is.

at our reception pc we have (at the minute) a database that handles what i want to create. these databases cost 1000's and we need one for our other premesis.

i think i know what i need to complete the core of the database

from start to finish this is the core

customer books in a service(the service will be displayed on a diary view)
the customer arrives to have the service
the customers' service is complete and ready to pay
we receive payment and process the transaction

now this is a simple way of explaining what i want to achieve and all other aspects of this database will derive from the core.

i have danced around this for sometime and now i really could do with nailing it.

so first i need a diary can anybody help with that.

all i will say is that databases cost 1000s for a reason, you WILL strugggle to build in the complexity of what you already have. their developement costs (time) are split over all their users - and yours arent.

if there is something out there that already does what you want, you would be better trying to negotiate them down, in the first instance.

maybe it depends how many thousands, though.

 

[shutzy]

please gemma, do not state that i cant build a database in the complexity of what we already have. i used to think that but now i think, why not? every time i used the software i am analysing what it is doing to be able to learn more.

as for it costing 1000's for a reason. yes you are correct but that reason is that they run it as a business. i see the software suppliers at trade shows. do you know how much per sqr ft it costs? like in any walk of life if you want to run things as a business it will cost more and you will have to charge more.

now a little bit of why i know i can build in the complexity of what we already have. we had updates from them for a year. it cost £40 +vat per month and what did we get. 1 update. also most of the facilities on the software do not work as they are intended. to add further, online appointment booking. they had a pathetic effort of trying to create rubbish. i look around at all the other software suppliers and find that how hard is it. its only php.

you may think that i am trying to make everyones job sound easy. i know it isnt but with people like me and forums like this, anyone who is willing can do.

i am not bothered if it takes me 6 months to get the basic functions of what i want to achieve. that goal i have nearly reached. i am not doing this to save money as a first thought. i am doing this to achieve better than can be provided without a fully customized paid for software. when you buy off the shelf it is catered for as many as possible. there are also so many functions that are obsolete and we have never used that the software is too complex. the developers that built this software are not in the industry. they do not know what we need day by day. i do. therefore i would think that i am better placed to build a database for myself. i dont plan on building for others other than sharing what i have created.

thankyou for your input but i do feel that it is somewhat bias.

 

[CBrighton]

A database is simple to create.

A database that does everything that you want is less simple.

The simple fact that you are here asking for help rather than doing it yourself shows how complex it is for someone who doesn't build them for a living.

However, if you want to do it yourself...

customer books in a service(the service will be displayed on a diary view)
the customer arrives to have the service
the customers' service is complete and ready to pay
we receive payment and process the transaction

These are the processes which you still need to create, correct?

 

[shutzy]

yes. i fully understand that i dont know everything. i am willing to learn. after all the programmers didnt get out of bed one day and know how to do it.

yes there are elements in my database that i still do not know how to create. i think i really need to get a hold on vba for me to progress further.

the next crucial aspect i think is a diary view.
i already have a tbl that records the customers appointments. i just need to know how to put that information onto a diary. i imagine it to be like a spread sheet. with each row being 15min intervals. hopefully but not running before i can run multiple columns for different staff.

the tbl i have created so far is like this

ClientTreatmentsID / ClientDetailsID / TreatmentsID / DateOfTreatment / TreatmentTimeID / TreatmentNotes / EmployeeListID

in the diary view i would like to display the client name(via ClientDetailsID) the treatment that the client is booked in for(via TreatmentsID) the time that the appointment is booked in for (via TreatmentTimeID) and if there is multiple columns the appointment in the correct column (via EmplyeeListID)

i do hope i make sense. i know i ramble and loose my train of thought.

thanks

 

[CBrighton]

Sounds like a cross tab query may give what you want (i.e. staff names = column, 15 min interval = row, booked appointments = data) but I'll let someone who uses them come in and explain more (I don't really use them myself).

In the meantime you could play with the crosstab query wizard to see if you can create what you want or look online for guides on crosstab queries.

 

[shutzy]

thanks. ill check out 'crosstab queries' dont know what they are yet.

 

[gemma-the-husky]

please gemma, do not state that i cant build a database in the complexity of what we already have. i used to think that but now i think, why not? every time i used the software i am analysing what it is doing to be able to learn more.

as for it costing 1000's for a reason. yes you are correct but that reason is that they run it as a business. i see the software suppliers at trade shows. do you know how much per sqr ft it costs? like in any walk of life if you want to run things as a business it will cost more and you will have to charge more.

now a little bit of why i know i can build in the complexity of what we already have. we had updates from them for a year. it cost £40 +vat per month and what did we get. 1 update. also most of the facilities on the software do not work as they are intended. to add further, online appointment booking. they had a pathetic effort of trying to create rubbish. i look around at all the other software suppliers and find that how hard is it. its only php.

you may think that i am trying to make everyones job sound easy. i know it isnt but with people like me and forums like this, anyone who is willing can do.

i am not bothered if it takes me 6 months to get the basic functions of what i want to achieve. that goal i have nearly reached. i am not doing this to save money as a first thought. i am doing this to achieve better than can be provided without a fully customized paid for software. when you buy off the shelf it is catered for as many as possible. there are also so many functions that are obsolete and we have never used that the software is too complex. the developers that built this software are not in the industry. they do not know what we need day by day. i do. therefore i would think that i am better placed to build a database for myself. i dont plan on building for others other than sharing what i have created.

thankyou for your input but i do feel that it is somewhat bias.

sorry - it wasn't intended to be biased - just pointing out that building database applications is not trivial, and not at all like building a spreadsheet.

if you are willing to spend the amount of time it needs you should get the result you want, and you will certainly learn a lot - so good luck.

there is certainly a trade off between time spent on this, and on other things you could be doing.

i am sometimes surprised at how poor some software offerings are - you can certainly get locked into stuff that isn't right, and keep wasting time persevering.

as others have said, the key thing to get right is the data structure. everything flows from this. you will almost certainly also need a lot of competence in VBA.