Using VBA or macros to build a database

[Access_Novice]

The Microsoft websites suggests using macros if you publish a database to the web. But what about this. Suppose I want to add an Access database to Google Drive. Then my coworkers can access the Access database when they need to.



In this case, should I use VBA or macros? Please give pros/cons. Better to find out before I start.

[June7]

AFAIK, publishing db to the web via SharePoint requires macros and a web db design. I've no experience with Google Drive. How do coworkers work with the database? Is it a split design? Do they open frontend on their local computer that links to backend on Google Drive? Or is it an all-in-one db and users all open the same file?

[Access_Novice]

AFAIK, publishing db to the web via SharePoint requires macros and a web db design. I've no experience with Google Drive. How do coworkers work with the database? Is it a split design? Do they open frontend on their local computer that links to backend on Google Drive? Or is it an all-in-one db and users all open the same file?

I was planning on loading the entire database file to Google Drive then they can access it. Sounds like that does not mean splitting the DB.

[June7]

Multiple users opening the same file can be problematic. I have one db used this way on our network server and no issues yet but still have fingers crossed.

[Access_Novice]

Multiple users opening the same file can be problematic. I have one db used this way on our network server and no issues yet but still have fingers crossed.

Ok. Do you have any suggestions to deal with this?

[June7]

Ideally, multi-user db is split. I don't know anything about Google Drive. I have seen several threads on the topic. Try search.

[TimF]

Ok. Do you have any suggestions to deal with this?

Yeah you have a front end that all users can have their own copy of, and instead of a table in that database you create your table in another blank database and link the front end to the table in the other database.

It is easiest to develop your database with the table unlinked, then before releasing it to users just create a copy with the table and create the link.

[Access_Novice]

Ideally, multi-user db is split. I don't know anything about Google Drive. I have seen several threads on the topic. Try search.

Cool. I will keep that in mind. Thank you.

[Access_Novice]

Yeah you have a front end that all users can have their own copy of, and instead of a table in that database you create your table in another blank database and link the front end to the table in the other database.

It is easiest to develop your database with the table unlinked, then before releasing it to users just create a copy with the table and create the link.

Thank you. Good explanation. So each user has their own copy of the front end. But what if two users access the same table (in the backend) at the same time? Can this cause problems? Can this be prevented? How does Access deal with this possibility?

[June7]

Accessing same table is not an issue, editing the same record can be.

[Access_Novice]

Accessing same table is not an issue, editing the same record can be.

1. Awesome. This is very helpful to know. So if accessing the same record is problematic, how does Access deal with this?

2. Suppose one user is already working on record #75. Then sometime later a second user attempts to access record #75. Is it possible to program Access so that when the 2nd user attempts to access record #75, they are not allowed to and they receive a message box saying that "another user is already working with the record you are attempting to access, please wait and try again later."

[June7]

Access will notify users if they are attempting to edit same record at same time. One user's edits will not be saved.

Doesn't actually have to be two people editing the record. This conflict can be instigated by bad programming.

We really don't have this issue. Our business process makes it very unlikely for more than one person to be working with the same record at same time.