Connect to Access DB to update or build table
sorry if this is a duplicate post
Okay, I am using some older tools here and what I want to do should be easy but I can't get the connection right.
I have a access.db setup on a godaddy hosted website. I am using frontpage 2000 to build the website and manage a few things.
The DB works fine for day to day use but I am trying to update it daily with changing records.
Thru frontpage currently, when I open the db, it downloads it to a temporary folder on the computer I am working on, I do my edits and my uploads (imports) of the new data and then close the database (access) and then the entire db is then reuploaded to the website with the changes.
Essentially what I am now doing is taking the data from another program, exporting it to a excel file on my computer and then opening frontpage, opening the access db on the website by clicking on it, waiting for it to download, and then running a macro I have prebuilt that takes all the data from the excel file and imports it into a table with the parameters I have already set up. The table is essentially deleted as the first part of the macro, then rebuilt with the new data for which a bunch of pages run off of. The access part is literally one click to activate the macro, one to run it and one to close access. I then close access and frontpage uploads the db back to the website and voila, the new info appears.
What I would rather do is either have a page on my website where I can upload the file and then have the website db server (again, not my hosting , godaddy) take the file and rebuild the table or somehow get it from my local computer to the website access db without opening frontpage and just pushing a button or something.
I thought maybe I could come up with a connection string to replace the table in the access database, but I don't know if I can get that to work because the import function now takes place in access. I am pretty proficient in db stuff, just not that great in getting the web to computer route.
My two thoughts are
- Upload the excel file and click a button on the website to have it rebuild the table.
- Run access locally? and import the new data into a table and then have that table link to the table in the db and write the new information.
Because the info changes so dynamically, it doesn't make sense to just append the new entries, because there may be new entries and tons of edits or other entries that are deleted at each upload, so it makes more sense to just redo the table at each step.
Any thoughts or ideas on the easiest route to accomplish this?