Share a database? Am I on the right page?

[ReAn]

Here's an example.

db22.mdb would be the one on your network.
db23.mdb is the db on your computer with everything else. (Open this one for code/example)

Boox1.xls is the file you would replace each week.

I had mine all in c:\ so give it a shot there (so the linked tables dont screw up atm)

Ok?

 

[zuberal]

Thanks ReAn, I really appreciate that.

I had a look and I think I was on the right track, although I had put the Append query in the database that would be on the network. I'll play around with it tomorrow, and figure out how you did it. Especially that nice udpate button!

I'll let you know how I make out!

 

[ReAn]

Sounds good.

 

[zuberal]

Thanks ReAn, I really appreciate that.

I had a look and I think I was on the right track, although I had put the Append query in the database that would be on the network. I'll play around with it tomorrow, and figure out how you did it. Especially that nice udpate button!

I'll let you know how I make out!

I guess my logic was to keep excellinked and excel query updater on the network database so that all of the admin stuff can be done there, so the end users don't see it. I think that will work too...

 

[ReAn]

Ah, your method would work just fine.

 

[zuberal]

Most excellent,

I think I have my head around this now. Couple things left...

When I run the append query, it adds the new records from my excel spreadsheet. (as long as something is entered in my primary key field which is the 9 digit number) Although, I still get the error which says that some records were not brought in due to key violations. This error is gone when I use my little form button with your code. (errors off) Although, the records that I update in the spreadsheet are still not reflected in the table after clicking the button.

Is the function of the append query to just add records to the table that don't exist via the primary key, or can it also update records that have changed regardless of primary key. Am I missing something? You mentioned something about an ID before...

Also, your button is all nice and centred in the screen when you run it. Mine just opens up and is way up in the left with a big grey background. My settings seem to match yours...where can I fix this?

Thanks again ReAn!

 

[ReAn]

All it does is add the new ones.

Match the form properties, it might help out ^_^

 

[ReAn]

Just read that link above, let me modify my query to update records as well.

 

[ReAn]

Try modeling your query like this one: may have to re-link tables...

 

[zuberal]

Just read that link above, let me modify my query to update records as well.

Thanks! ...I'll check it out. Which link above are you referring to? I'l check the form properties again...I thought they were matched...

A~

 

[ReAn]

the linked tables in my test db(s) for they may be absolute which would mean you need a user 'ablondah' on you're machine and a folder 'asdasd' on the desktop for you to put it in if you want it to work.

However, it may be storing the links as relative paths, in which case you are set.

 

[zuberal]

I found your stuff just required c:\blondah\my documents to work...so I'm good to go.

Looks like an update query. Again, I have to leave the office. Finally got my drive space which I've been waiting for for awhile. Looks like we're all set.

Much appreciated. Where do you live? Maybe I could send you a donut? Or a beer? lol

A~

 

[ReAn]

Haha, no worries about that, but you know my location is valid look up ^^^

 

[zuberal]

Haha, no worries about that, but you know my location is valid look up ^^^

Hello Dangerous programmer!

Calgary huh? Never been there!

So OK, I've tried both queries. The update one...you have to put all the fields. Works pretty good.

I guess there is no way to combine them into one big query, with one big button? Rather than two queries, and two buttons?

PS: How did you get that button to first pop up when you start Access?

 

[zuberal]

Hello Dangerous programmer!

Calgary huh? Never been there!

So OK, I've tried both queries. The update one...you have to put all the fields. Works pretty good.

I guess there is no way to combine them into one big query, with one big button? Rather than two queries, and two buttons?

PS: How did you get that button to first pop up when you start Access?

Hmm...I think update the code....I wonder if I could do that....can I just copy your code and just change the name of the new query in the code...just append it to the bottom?

 

[ReAn]

1) The update query if set up properly will update old records AND add missing ones.

2) Tools->Startup, Choose initial form.

 

[zuberal]

1) The update query if set up properly will update old records AND add missing ones.

2) Tools->Startup, Choose initial form.

AH! Nice long weekend.

Thanks for all your help ReAn...I'm starting to build the "real" DB now...then will continue on testing in the other.

Quick question in regards to your update query. May be a bit dumb..lol

In design view, I noticed that you have the table as the main table, and you have the spreadsheet in the "Update to" field. In other words, take the info from the table, and update it into the spreadsheet. Isn't that backwards? The info in the other append query is opposite. Or is this how the update query works? I already tested and it looks like the data comes over fine to the table from excellinked...although, when I update data in the forms and run the query, will the data also go back to the spreadsheet when the query is run? lol

 

[zuberal]

1) The update query if set up properly will update old records AND add missing ones.




2) Tools->Startup, Choose initial form.

I think I'm going to leave this as an update query and an append query.

In regards to the form, is there a way to make it dissapear or confirm and disappear after clicking the update button?

Also, I created two Advanced filters called "delete cities 1" and "delete cities 2" that run off of the main table. I saved them as queries. When we run each one, the results come up and then we delete the records. (then we confirm delete) Is there a way to automate this where they run and don't need confirmation at all? Will this require more code?

Thanks! (gonna owe a 24 before this is all over hehe)

 

[zuberal]

I think I'm going to leave this as an update query and an append query.

In regards to the form, is there a way to make it dissapear or confirm and disappear after clicking the update button?

Also, I created two Advanced filters called "delete cities 1" and "delete cities 2" that run off of the main table. I saved them as queries. When we run each one, the results come up and then we delete the records. (then we confirm delete) Is there a way to automate this where they run and don't need confirmation at all? Will this require more code?

Thanks! (gonna owe a 24 before this is all over hehe)

Hmmm...I just found delete query...perhaps that will be better for what I need to do...

 

[Pat Hartman]

Is the function of the append query to just add records to the table that don't exist via the primary key, or can it also update records that have changed regardless of primary key. Am I missing something?

YES, you are missing something. The reference that I posted is a SINGLE query that will BOTH update existing rows and add new rows.