Adding records

[Srin]

I have a database which has a lot of tables. Many of these tables are related but I didn't create the database and do not understand the logic that was behind making the relationships.

However I am working on data involving Customers within the database.

Some tables have customers with their contact data, other with the products they ordered and others which have just all the customers in them etc.

If I were to add a new customer or a product in the "customer" or "products available" tables, how can I make sure that they are reflected in the remaining tables.


Also we get a monthly data dump which I upload into the database that has customers in it too. I find this task a bit daunting. Any help on this will be very much appreciated.

Thank you.

[JamesDeckert]

Hopefully whoever created the DB also created forms so you can input data and don't need to wonder about the underlying tables.
Also, did they create a users manual?

[Srin]

Fortunately for me, they did not. Also some of the Forms that were being used to input the data do not work (Corrupted, or leads to tables that are not in the relationship). The person who was working on the database is no longer with this project and left midway.

[ItsMe]

I would start by diagramming the table structure and their relationships.

[Srin]

I sound like I am trying to make this difficult. The relationships button in Access is not highlighted meaning I am unable to click or open it. This is a database that is hosted on a SharePoint site and went corrupt when they updated the servers or Access version.
I know the db was designed on Access 2010 and subsequently managed in 2013. But started behaving weirdly in between.

Is there any other way to find out about this? Or for me to do create a form that fills in all the respective tables that have this "Customer" field in them. Or to figure out relationships by any other way.

[Srin]

I tried importing the database to create a local copy and work on it trying to find out the relationships and object dependencies on the various forms, but it says this:

"The database has been placed in a state by user 'Admin' on 'some' machine that prevents it from being opened or locked."

Now, I cannot even make a new database to work on, or if it gets really bad to start using the local copy.

[The Admin in the said statement is no longer with us.]

[ItsMe]

Access 2010 web apps and Access 2013 web apps are two different animals. IIRC, 2010 web apps use the accdb file extension and link to Lists in SharePoint and 2013 web apps are converted to some sort of Microsoft SQL version of database that is hosted by SharePoint Server With Access Services.

I think you should be less worried about forms and, instead, try to get a hold of your data. The data may not even be associated with SharePoint or some data may be in an accdb file while other data is in a SharePoint List.

[orange]

What is the database about? What business is the database "attempting" to support?
In order to understand the database and relationships, you need to have a clear statement of the business rules and processes.
So creating a clear, concise description of the business and the things involved would be an excellent start. I would then create a data model based on the business facts.
Then compare this current model with the existing database-- then make decisions on next steps.

[Srin]

Both @ItsMe and @orange, thank you for your input. The database is used to host customer information such as addresses, contact, orders, product and its information. Most of the updated data is obtained from the data dump generated every month. How we put it into the db is by exporting the excel data dump (After changing Cell formatting, arrangement etc) and then renaming it into a table that originally hosted this data (And deleting the old one, at least for this, there is a Word file explaining how to do it).

A lot of other data such as new customers, and information generated after meetings is input manually using forms for the purpose. Some of these forms are corrupted or point to tables and queries within the database that do not do anything.

Also one big confusion from my side is even if I import the data dump to the db and then rename it, how will it work (Is it the right way?). And all the data that we enter manually has no way of making it into the table that is imported (Not that I know of) or tables, queries and forms that make use of the imported data dump table.

If I were to make a new database which seems likely at this point (unless I can do anything about the existing one), how do I make one. Because the Customers and Products do not have any IDs to be Primary or Foreign keys. The orders have IDs that are generated in the data dump. Currently a combination field of "Customer & Products" is used to reference to the right projects which is a round about way of doing it.

Please suggest a correct approach that I may take in creating a new database or managing the existing one.

Thank you very much for your continued input and suggestions.

(The extension on this Db is .accdw)

[ItsMe]

An accdw file is a shortcut to the actual database hosted by Access Services. I do not know anything about Access Web Apps or SharePoint. I only know enough to understand a high level view. My advise remains the same. Get a hold of your data. Then, understand how your data is structured. Use pencil and paper to diagram the data structure/tables.

When you say the database is deleted after each import process, this may be true. However, it seems very unlikely that a database would be deleted. It may be that the data within is deleted. I suggest you hire a consultant to help you get a handle on things. You need to figure out where your data is.

[Srin]

I apologize if I was not clear. The db is not the one deleted. The table holding the old import data dump is the one deleted, and the new imported table is renamed to the older one.

I can import the tables to a new database but not the queries and forms as they are restricted by the previous Admin. So I do in fact have the data but do not know how to find out the relationships as they are not imported with the tables.

[ItsMe]

It is likely that SharePoint's Access Services are restricting your privileges to the main database. My understanding is that when you launch your accdw, a copy of the database is placed on your local machine. Perhaps you can find this file within your User's folders and make a copy of it. Once you have a copy of the actual database, you might be able to glean insight by taking inventory of the tables and looking at the JOINS employed by Query Objects.

[Srin]

Based on your suggestions, I tried the same. It still does not show relationships. Which might be the best solution to start from here?

If I were to start a new Database, would it be better to give custom Codes or IDs to Customers and Projects or is there any other way that will give me better results?

Thank you.

[ItsMe]

None of my databases use the Relationships window in Access. If you looked at one of my databases, you would not see any Relationships if you opened the Relationships window. You would have to refer to an ERD I created and the code behind to understand how the database and application works.

If you want to decipher an existing application or create your own, you will have to study how relational databases work. This link may provide some insight.
https://www.accessforums.net/access/...tml#post221691

[Srin]

Thank you @ItsMe. I found it extremely helpful to say the least. I will start with my own database building project, while I still continue to work on the current db. It was insightful information.