New User: ADP vs Ms Access database

[k1dude]

Can someone please help me out here.
I am New Access User with some programing background
I am starting my own structural engineering company, and like to create an basic application and database for my projects.

Example: Design a Beam, input known data, access material database, calculate require data, and report the results


I underestant that ADP doesnt contain any Data it contencts to SQL server database.

here are my Questions:
What is the best and easy way for me to do this.

Consideration
Creating Form Using Fallowing:
Access, InfoPath, or VB

Creating Data in:
Access, XML, Excel

Creating Report
Ms Word

I only have single computer for know but like to be able to share that data in network for future

Thank You, Please let me know if there is a better software or procedure for me to get to my Goal!

 

[ChopinFan]

Hi K1,

Unless you are going to store a very large amount of data, have many concurrent users, or need to perform operations that Access cannot, I see no reason to go with an ADP/SQL combo. I would just design a regular .mdb file for now and potentially migrate to SQL in the future. You will find using SQL server to be far more expensive (purchasing the server and licensing, etc.) and if your business is just getting off the ground, you may not be able to afford this now. It sounds like using SQL server for your current needs is overkill.

Just a thought.

 

[k1dude]

thank you i will go with .mdb

i try to create my forms and DB in Access how ever for the report, i like to use Ms Word to design my report since i need to add many text to my report beside design data, is it posible to connect to Access database through Word?

also for Alt way i am not sure if this is right place to ask or not, since i already know how to access XML data through word is it good to use InfoPath to store my data in XML format and create my report in Ms Word?

Also have difficalty understanding this i have two type of database 1. is general database which i use for all projects 2. is project data which only usefull for that specific project, is it ok if i create new database file for each project i have so i can place them in project folder along with their drawing (CAD) files, or is the best to create all in one .mdb

 

[ChopinFan]

You can certainly program Word to pull data from your mdb file to populate whatever template you create. As for Infopath, I've never used it so I'm afraid I can't help you on that.

Without my knowing all the data you want to store I can't say which method would work best for you. It sounds from your comments like you do not need separate databases, but it is a normal one-to-many relationship between your projects and details about each project, which would be stored in two separate tables in the same database, unless the memory size of the CAD files is too large for a single database. Once again, I can't be sure since I don't know all the information of what you are trying to do.

 

[k1dude]

Thanks again for responding

Let me clarify it little bit

One set of database I need, “call it general data” consist of Building Code Specifications and Construction Material Specifications, example: Material: Wood, Type: DFL #1, MC: 2.4, …

My general Data will be store in one .mdb file with forms related modifying material property, adding new material … forms related to General Data, and I do not need report for this

My second type of data is related to each project. Example: Project Name, Number, and most important data related to component I design which I need to input some data do some calculation on them and retrieve data. to do this I need to get some data from general database.

The way I currently manage my information instead of having a general data I have printed tables which I have to go through obtain information manually, for design part I just use Ms Excel to do my calculations using data from tables and then write my report manually in Ms. Word so copy-past that calculation from excel to Ms word.

I have a Project Folder under that I have Folders for each project Example C:\Projects\P0102\
I place all my Ms. Word, Ms. Excel, and CAD drawing inside that folder.

I don’t want to Place my CAD files inside Database, I like to place information and data related to same project inside that project folder along with CAD files and reports. How can I do this.

 

[lameid]

I would use one database... In this case you are thinking of keeping things separate because they belong in separate tables. Properly separating data into tables is called Data Normalization. You can find the rules of Data Normalization at

http://www.datamodel.org/NormalizationRules.html

While I would use Access, I would use a split setup where data is in one file (backend) and the application objects are in another file (frontend). In this scenario all the tables in the frontend are linked to the backend. There are a few reasons for this. You can beck up the files separately. If you make a bunch of application changes, you may want to back it up immediately and leave backing up the database to a regular backup job. The other reason for going split is that if an application object gets corrupt or corrupts the file, your data is safe and you can quickly restore the application without worrying about your data. This was more likely to happen in older versions of Access and especially in a network environment.

Just in case you ever get really big a few guidlines will help you more easily move to SQL server if you have to...

1. DO NOT USE SPACES IN TABLE OR QUERY NAMES
2. DO NOT USE SQL SERVER KEYWORDS AS FIELD NAMES (you would only know what they are if you were experienced with SQL Server)
3 AVOID USING ACCESS FUNCTIONS IN QUERIES
Some Access functions have counterparts in SQL Server while others do not. Even if there is an equivalent, the upsizing wizard may not be smart enough to do the conversion for you.

The first guidline just makes life much easier in dealing with SQL server, as Access but more so SQL server. The second is absolutely required. You probably would not hit a keyword on accident but the one that bit me was Index. I ended up renaming the field to PricingIndex in a couple tables and countless queries, forms etc. Finnally the third, really is only a suggestion. It was the most tedious part of my migration to convert queries that had to be adapted to SQL server functions. I was lucky though, most of them converted. There were a couple of instances where I had to move some format functions to reports (or create procedures to store data that way).

I hope I did not overwhelm you... I got a little over the top technical for future visitors that may find the post looking for things to consider when designing forward looking to SQL server.

 

[k1dude]

Thank you both for helping me out, I will try to do as you suggested and maybe get some reading material to understand Data modeling better.

Thank you again.