Share a database? Am I on the right page?

[zuberal]

Hi,

I've recently been thrown into Access again and haven't touched it for years. They need an Access form that rides off some data that I import from a massive excel spreadsheet.

Although I know that you can share excel spreadsheets between two or more people, can this be done in Microsoft Access? Or am I going to have to purchase something like SQL Anywhere?

Also, in terms of relationships...if I create a single table, then create queries from it, and users fill in the information in a form drive off of the queries, will this update only the query, or will it also update the table? Is this something I would have to setup in relationships right off the bat? Cause I'd like to have all data saved back to the main table to run reports from it.

Goal -> small and simple.

Thanks for your help!

 

[KenHigg]

Just put the .mdb file on the server and rock and roll. Access handles the basic multiple user stuff itself. You can have several user working in it at the same time...

The table will get updated...

 

[zuberal]

Help!

Hi!

Thanks for your reply!

Actually, there will be no server for this setup. We're going to be running it off of a shared network drive. (with multiple users running the DB from the same location)

Currently in testing I'm getting the error "Microsoft Access can't save design changes or save to a new database object because another user has the file open. To save your design changes or to save a new object, you must have exclusive access to the file". That is when I try to edit records in a form. (assuming the form auto updates the table)

It's weird cause I have the DB to open in shared mode and no locking on both machines. (tools. options, advanced) I see in the share help file that the actual folder needs to be shared, but I cannot see how this would matter. (I would have to contact admin to get him to do that) Would that affect it?

Can anyone help?

On a side note, I assume that if I were to run queries from the main table, then run forms from those queries...when you update the data through the form it will update both the query and the table? Or only the query? Relationships? Boy, I need to get reading.

 

[ReAn]

Open a new database, import all your tables into it. Save it on the shared drive.

Open your original database (make a backup copy too) and delete all the tables. Now right click and choose "link tables..."

When you have doen this, enter in the absolue path to the shared drive:

aka: \\myservernamehere\myfoldernamehere\myfilenamehere.mdb

Select all the tables and hit ok.

Now all your DATA is held elsewhere (on the server). Now send the front end database (one with all the queries/forms/etc...) to all the people on the network. They now shouldn't have any problems with accessing the dbs.

 

[monkeytunes]

Currently in testing I'm getting the error "Microsoft Access can't save design changes or save to a new database object because another user has the file open. To save your design changes or to save a new object, you must have exclusive access to the file". That is when I try to edit records in a form. (assuming the form auto updates the table)

This isn't a data thing - this is an 'object' thing. Access won't let you change the appearance of Forms while someone else is using the database. Two users can modify data to their hearts' content, but not the structure (i.e. Forms) of how you view the data.

The solution is to do what ReAnn suggesting, and split the database. If you look under Tools->Database Utilities->Database Splitter in the top menu, you'll find a handy, easy way of splitting the database. Make a backup (ALWAYS BACKUP! BACKUP BACKUP BACKUP!), then put your database on the Shared Drive and then run the splitter. Access will move your tables to a Backend (which it will rename "YourDatabaseName_be.mdb") while keeping all your queries, forms and reports in place (this is now your Front End).

Then make sure all the users have a copy of the Front End on their desktops. Now, the copy of the Front End on your desktop can be modified all you want, and when it's time for an upgrade, you just upload your new, modified Front End onto all the users computers (again).

On a side note, I assume that if I were to run queries from the main table, then run forms from those queries...when you update the data through the form it will update both the query and the table? Or only the query? Relationships? Boy, I need to get reading.

The query doesn't hold any data; it merely tells Access what records you want to see (from the tables you specify). In a nutshell:
Tables store data,
Queries pull data,
Forms display data,
Reports present (good for printing) data.

Anytime you modify a record, whether it's in the results of a query, or from a form, or directly on the datasheet view of a table, the record is modified in the table, because that's where records are stored.

 

[ReAn]

Off topic: I think ill keep a counter on how many times people spell my nick wrong, even when it's right in front of them... On these boards.. so far 4 times...

 

[monkeytunes]

Funny, MS Word tells me that "ReAn" is spelled wrong, too.

 

[zuberal]

Ok!

OK,

"ReAn" (lol) and monkeytunes, thank you very much for your help.

I didn't expect your fast replies and did a bit more homework and learned about the object thing. I figured out that I am able to update records no problem, and queries as well. So forms are objects eh? I find it almost flawed that you can't have one db, with forms running off of that DB, and you then edit the "data" within those forms, click save and it updates the table. It's not like I'm changing what the form actually looks like or anything. But then as you say, forms "display" data. Still, as edited data in the queries seem to update the table, you think that edited data in the forms would update the table as well.

I appreciate both of your options. I'm going to attempt both. (I know they both do the same thing, but I need the practice!)

Thank you!

 

[ReAn]

Funny, MS Word tells me that "ReAn" is spelled wrong, too.

Yeah well, that's about all you can expect out of Micro$oft

*cough*cough* did i say that... ???

¬.¬

 

[monkeytunes]

I didn't expect your fast replies

If you hang around awhile, you'll find that many of us patrol/troll the boards all day, and with users scattered all around the world (Europe, Australia, N. America, etc.), there's almost always someone handy to help.

I find it almost flawed that you can't have one db, with forms running off of that DB, and you then edit the "data" within those forms, click save and it updates the table. It's not like I'm changing what the form actually looks like or anything. But then as you say, forms "display" data. Still, as edited data in the queries seem to update the table, you think that edited data in the forms would update the table as well.

Well, you don't actually have to click Save to save your data, or changes to data. Access updates your records as soon as you change them, which is contrary to what most users are used to, since everything from Notepad to Excel will ask "Do you want to save changes?" Access will only ask you that if you've changed an object (Table structure, Query structure, Form structure, Report structure).

If you want Access to act like other apps and ask you about saving data changes, then there is an option under Tools->Options->Edit/Find tab. In the upper right, you can ask Access to confirm "Record Changes".

There are also ways to limit record change confirmation on a form by form basis, using Visual Basic code (which I can't take credit for, since I took it from somewhere else on the board). I can post an example if you're interested, but I think the Tools menu should solve the problem for you.

 

[ReAn]

You could try his way, looks interesting, but I've use the method I described here at my workplace and it works wonderfully.

 

[zuberal]

Thanks!

Well everyone,

I thank you all for your responses. I think I'm gonna go with Monkey's way of doing it....cause it's very simple. ReAn! You should give it a whirl too...it does the exact same thing...but it's a little more simple. Just stick your main DB with all your stuff in it on the network, split the database to a local location, and give everyone a copy!

As for the saving and record changes...I think I'll turn that on for the "end users". They'll be a little more comfortable with it prompting them to save.

I have a biscuit for all of you! ;-) Alex~

 

[Pat Hartman]

Access updates your records as soon as you change them

- I hate to be picky but that's not tecnically correct. I believe that it is important for developers to know exactly when/how something so important happens. Otherwise, they will not be able to control it.

Access updates the current record AUTOMATICALLY when the current record pointer changes not when each field is changed as the above quote implies. This is caused in a number of ways. Some of them are:
1. Move to next or previous record via the navigation buttons.
2. Move the cursor from a main form to a sub form or vice versa.
3. Close the form.
4. Close the database

Hiding the current form or moving focus to a different form or report does NOT save the current record. Therefore, you may find it necessary to force Access to save the current record if you want the report or form that you are opening to reflect the current data in the form. To do this use:
DoCmd.RunCommand acCmdSaveRecord
If you want to present the user with a "do you want to save?" message, the ONLY reasonable place to put the code is in the form's BeforeUpdate event. This event is the last event to run prior to the actual update action, it is cancellable and it ALWAYS runs if the current record is dirty. The only way to prevent this event from running is a reboot. So, if your user answers no to your query, you can cancel the update with:

If msgbox("Do you want to save?",vbYesNo) = vbNo Then
    Cancel = True
End If

and Access will discard the changes.

 

[zuberal]

Side note..importing modified data

Thanks again for all your info!

On a side note...

The data in this access database is from an excel spreadsheet. Each week, we receive a new excel spreadsheet to import, which contains new data, and modified data.

I've added a primary key which makes sense. It's a 9 digit number. Therefore, when new data comes with a new 9 digit number it is brought into the database. Although my question is this...

How do I get the modified records into the database when for example, the 9 digit number already exists in the database and access excludes it from importing? Can it be done gracefully?

Thanks again!

 

[ReAn]

Make sure in you're table the ID is a primary key.
Link the excel spreadsheet and then rename the link to Excel-Linked

Make an Append Query that appends all the records from Excel-Linked to whatever your local table is, Name it qryExcelUpdater

Then make a Function (or build it in a clickable button the following code

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryExcelUpdater", acViewNormal
DoCmd.Close acQuery, "qryExcelUpdater", acSaveYes 
DoCmd.SetWarnings True

This normally would pop up an error saying x many records could not be added due to primary key violations, but our first line of code turns off warnings. So we are set!

 

[Pat Hartman]

You can use a query that will append new rows and update existing rows. Look at this article from the knowledge base to see how:
Updating and Appending Records in One Update Query

 

[zuberal]

Oh boy...

I think I'm dumb, but overall I think it's my lack of Access training and knowledge. So then this would then update the same records with new data regardless of the Primary Key? That's great! Now just to figure it out.

I find it funny that I can build computers, and have supported for a number of years, (network, server etc) but when I see code I run for the hills! lol I find that programmers explain code to support people like support people explain things to end users. Like they know what they/we're talking about! lol I do appreciate the help though, unlike end users. hehe

So you're saying make any primary key I want, as long as it is logical. (in this case the nine number id is the primary key)

OK, so instead of going File | Get External Data, | Import I would link the table (the table located externally on the network) to the *new* excel spreadsheet to be appended? When you say rename the link, is that within excel, or is that an actual file that is created? (or the spreadsheet file name?) I'll jump into the help files.

Hmm...Append Query...I'll jump into the help files for that too.

Functions...that will be something I have to look up to. Is that an extra added button you add to the toolbar in Access? Or would this function be built right into the query? Whew!

A~

Make sure in you're table the ID is a primary key.
Link the excel spreadsheet and then rename the link to Excel-Linked

Make an Append Query that appends all the records from Excel-Linked to whatever your local table is, Name it qryExcelUpdater

Then make a Function (or build it in a clickable button the following code

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryExcelUpdater", acViewNormal
DoCmd.Close acQuery, "qryExcelUpdater", acSaveYes 
DoCmd.SetWarnings True

This normally would pop up an error saying x many records could not be added due to primary key violations, but our first line of code turns off warnings. So we are set!

 

[zuberal]

You can use a query that will append new rows and update existing rows. Look at this article from the knowledge base to see how:
Updating and Appending Records in One Update Query

Hmm...that looks like you would create a new table then do an append query with that table being linked to the first. Two methods, both worthy of figuring out.

Thanks!

 

[ReAn]

I think I'm dumb, but overall I think it's my lack of Access training and knowledge. So then this would then update the same records with new data regardless of the Primary Key? That's great! Now just to figure it out.

I got my job in May? (I Think) I haddn't even touched access (well maybe once and was like... wth is this.. and quit.) and i picked it all up from there.

OK, so instead of going File | Get External Data, | Import I would link the table (the table located externally on the network) to the *new* excel spreadsheet to be appended?

Link a table to like c:\mysheet.xls and every time you get a new one just put it there.

When you say rename the link, is that within excel, or is that an actual file that is created? (or the spreadsheet file name?) I'll jump into the help files.

Left click on the linked table. Wait 3 seconds, click once again... now change the text.

Hmm...Append Query...I'll jump into the help files for that too.

Query Menu->Append Query...->Select table to append to

Functions...that will be something I have to look up to. Is that an extra added button you add to the toolbar in Access? Or would this function be built right into the query? Whew!

All I meant was like you make a button, right click->Build event->Code Builder->Paste

Simple.

 

[zuberal]

Thanks!

OK,

Let me see if I have all this straight.

1.) I have my Database, with user queries and forms located locally, and the main Table called (e.g) Teletable located on the network.

2.) I go to File | Get external | Link tables to which I link to the spreadsheet. (which I replace with the same name on a weekly basis) I rename this linked excel to excellink.

Make an Append Query that appends all the records from Excel-Linked to whatever your local table is, Name it qryExcelUpdater

3) I create an Append Query, selecting the database I'd like to append to. At the beginning for "show table" I choose both tables? (teletable and excellinked) Then I copy over the fields etc and run it. When I run the query will it ask which database I'd like to append from?

4) I create the button with your kind code to which I can just click each week and it will update automatically. Where do I place this button? I tried right clicking everywhere to see event builder! I'd imagine I would do this in a form?

Again, thanks for all your help!