Beginner - Need help with direction I need to take with Database...

[AdamLeeZ]

I am building an Access Database for my department to track projects and tasks. I am finding it to be a bit cumbersome. Maybe I am in over my head? I've never built a database before... Here's what i'm trying to do...

• Set up multiple departments with access to the database with security.
  
  • R&D, Purchasing, Master Data (within R&D)
  • Every individual will get their own loginID & password
  • Need security setup by individual to have access depending on their roles
  
  
• Will need to have multiple tables of data from our SAP system
  
  • Finished Good Table, Raw Material Table, Packaging Table, etc.
  
  
• The scope of this database will be for Food Scientists to track each of their projects by inputting standard tasks they complete, as well as a place for purchasing to request Bill of Material changes and new materials to be created. Project Managers will be able to manage all projects and timelines. Directors will be able to run reports on projects, etc.

Right now i'm lost at where to start... I have absolutely NO VBA knowledge. Maybe I should use a pre-built Access database and update the tables? What would you suggest I do? I don't have much time to figure this out. Maybe 3-4 months tops. I know what we need, and I can envision what it needs to do and how it should look. I just don't have the understanding of how everything fits together without any issues. Help?! Please kind words only.

[June7]

First thing is to learn the tools - relational database principles, programming concepts, Access functionality, VBA language/syntax and/or macro construction.

Start with http://www.rogersaccesslibrary.com/

Trying to customize a pre-built database can be more frustrating for a newbie than building from scratch. However, might get ideas from a template. Can download MS Desktop Project Management. Click File > New > Business > select db for download.

A multi-user db should be split design.

I am doubtful 4 months is enough time.

[AdamLeeZ]

In your professional opinion what amount of time would be needed to do what i've said needs to be done?

[June7]

Note that I am not a professional developer.

For an experienced developer, maybe several months. My primary db took 4 years from inception to full implementation - and this was my first which I inherited half finished and I had professional mentoring. I already had a basic understanding of databases and programming, just had to learn Access and VBA. I have built many other much smaller and less complicated that took anywhere from a few days to few weeks.

[ItsMe]

It aint cheap, but Microsoft Project may be useful. I would use Access because I know Access. So I do not have any idea how easy it is to accomplish what you need using Microsoft Project. I suspect one hurdle with using Project would be training the various users so they are familiar with Project.
https://products.office.com/en-us/pr...ion=&WT.srch=1

and maybe a third party add-in to connect to SAP
http://blogs.msdn.com/b/chrisfie/arc...-with-sap.aspx

[Homegrownandy]

Its taken me about 8 months to get to the point where I feel I understand access. Obviously I don't know it all and there are many questions I still ask on here.

As far as my project is concerned, I work on a priority based method of work. I had the database manage scheduling first, then costs/mail merge. It doesn't seem like it will ever be finished haha.

security is simple in a split DB as June suggested and I have no idea what R&D or SAP is so I cant help you there.

saying that, If you have a clear objective

[AdamLeeZ]

I have a feeling i am in the same boat with the amount of time it will take me to learn and understand Access.

R&D = Research & Development
SAP - SAP SE (Systems, Applications & Products in Data Processing) is a European multinational software corporation that makes enterprise software to manage business operations and customer relations. SAP is headquartered in Walldorf, Baden-Württemberg, Germany, with regional offices in 130 countries.^[2] The company has over 293,500 customers in 190 countries.^[2] The company is a component of the Euro Stoxx 50 stock market index.^[3]

[Homegrownandy]

possibly, but you don't need to understand access to achieve your needs. Just learn the relationship principles as June suggested, the rest you can learn as you go. My database is very complex and if yours is not it wont take you too long to get something working.

[AdamLeeZ]

ok. I guess maybe one simple question that I have is, what is the best way to create drop-down lists that multiple forms would be dependent on? Create tables for each list? or use the Lookup Wizard for each?

[ItsMe]

It is usually recommended to avoid using Lookup fields in tables. So it is likely best to create tables for your various lists, one table for each list. When creating tables, you need to understand relational database principles, like Normalization, Relationships, Keys, etc.

[AdamLeeZ]

My issue is that I understand what I need to do but the intricacies or details are blurry and it takes forever to find a simple answer to my questions. Like for instance...do I create ALL tables first before creating relationships? can I create forms or queries while I create tables or should I wait til all tables are built to build forms and queries, etc...

[ItsMe]

...the intricacies or details are blurry and it takes forever to find a simple answer to my questions...

It is likely to be difficult because you do not know the questions to ask. There will not be any instant gratification. If I were to tackle this I would probably block out three weeks just to determine the scope of the application and document the beginning of an application architecture. I would be happy to complete an ERD and have a few working queries in under two weeks. A project like this requires the development of the database to be isolated from the development of the application. These are two different animals. Access is unique because it is both an RDBMS and a Rapid Application Development tool combined into one.

Before you can concern yourself with a graphical user interface you will need to have a database. I start by writing an ERD down on paper and then use software to create an Entity Relationship Diagram.

Here is an overview of an ERD
https://www.youtube.com/watch?v=-fQ-bRllhXc

You will need to define entities.
https://www.youtube.com/watch?v=mxYy4OINoSo

You will need to define how you will connect to SAP. Most examples I have seen use ADO. If possible, I would look for an ODBC driver and connect using linked tables and DAO. In order to process CRUD operations via your connections you will need to understand Data types and how they will differ from Access to the Connection to the SAP server. Here is an example of data type comparisons of ADO. The example is a little overwhelming and likely more info than needed. However, it is important to consider data types when creating your ERD.
https://msdn.microsoft.com/en-us/lib.../ff822013.aspx

After that, you can start to create the application/User Interface.

[AdamLeeZ]

Excellent information. I am starting to define entities now. My concern is the connection to SAP...I would need buy-in from our SAP User Interface PM and I am concerned that without bringing him any database he will shut down the idea completely. Can I build the database without creating the connection to SAP until the last part of building it? This is a homegrown database for my department Research & Development. We are in the process of choosing a PLM (product lifecycle management) system but that's 2 years out before we roll it most likely. We desperately need something in the Interim.

[ItsMe]

I do not have experience with connecting to SAP. I would look for a way to connect using an ODBC driver. I do not know the capacaity of your SAP user interface PM, but usually a DBA aka database administrator, in conjunction with the IT department, will provide a "Connection String" and a driver download. The driver is installed on the client and then the connection string is used to link objects within an application/User Interface to tables inside the database.

If you talk to someone that manages an application or User Interface they may be able to provide something that is referred to an API. These usually use COM objects. So, instead of a connection string that would give you raw access to tables, you would use a library of classes to manipulate COM objects. COM objects will act as predefined commands to carry out actions like CRUD operations and or retrieve predefined datasets/Queries/Views. Instead of a driver that would provide an ODBC connection to tables, an API is supported by a library of classes. Sometimes an SDK is provided that installs custom Classes.

If the User Interface that is being used for SAP is homegrown, it is not likely the developer of that app took the time to create an API or is interested in people creating connections to the tables.

[AdamLeeZ]

Our SAP is NOT homegrown, but the access database i'm trying to build will be...