Queries

[Goh]

1) I am suppose to create data entry using vbaexcel to be able to generate a worksheet(in excel) that is able to show the data from the entry.
2) The information should at the same time be captured in a database(which is dunno which one is better) for retrieval in the future....this is only a small part of it (the data) and it will eventually link to other database tables.....

Matt see if you help to suggest the process flow ie..should i create a excel database to link to Acsess.or straight from excel/vba userforms to Access....
?thanks!!

[Matt_T_hat]

I see... hmmm...

That would produce a great level of redundancy...

You do know that Excel like forms can be created in access? For example the datasheet view looks very much like Excel.

The simplist way would be to use VBa to copy and paste the Excel data into the waiting Access database.

You can't record Macro's in Access as they are simply VBa and Macro's are something different... not sure that that matters but...

Perhaps if you said more about the project we may be able to identify ways of reducing the work load.

Excel V Access
Excel is for numbers
Access is for data with manipulation

Excel is flat
Access is relational


Q. how many Access tables do you plan to use?
Q. What data structure... if any?
Q. You do know that you can save Excel stuff and even move it to other work books, Paste Data / Paste Formulas / Paste: Link
Q. What do you mean by "a excel database" Excel is a spread sheet and Access is a database. Don't confuse them or you could end up in difficulties.

Did you know that Access can do near everything Excel can?

[Matt_T_hat]

Originally said by Goh
hi..
Assuming i have to use the vba in excel to create the userforms...
Cause i am dealing most with numbers: there are basically 3 sets(groups) of data:1)PURCHASE FORM 2)ENGINEERING hrs 3) budgets ..all the 3 sets are interrelated for their cals. and they deal with money most...
THere are forms that i am going to create for the three gps and the data cals are to be done behind the scene( of course hehe)



I believe there is such a database in excel.. ie the data is created and updated in a spreadsheet when a user enter entries from the forms...and ofcourse the list will increase..but i do not know if the excel database can be used w/o Access to be link up to many computers..?


i have to link up the database to a small office (abt 50 pple => 50 computers) and so that whovever enter the forms eg.pruchase forms would update the database...

Are you aware that you can SPLIT a database?

To do this means that you have a DB that contains only the tables this is stored in a central location such as a file server. The other DB is copied to the computers of all the workers. The "other" is known as the front end and is linked to the Back end.

Front End: Queries, forms, reports, macros, VBa etc
BackEnd: Data

One BE has many FE just like all the other relationships.

It is very easy to split a DB and highly recomended. Tools > Database Utilities > DataBase Splitter.

Same sub menu > "Linked Table Manager" - keeps your FE pointed at your BE.

That sound like your best option.

-Matt
BTW: PMing someone who answers posts is often seen as bad form.