General :: Updating From Local Database To Central Database
Sep 12, 2012
Database: Access 2007
I have designed a touchscreen input system using Visual Basic.net and this writes to an Access Database. Each Touchscreen has its database locally so it can still work even if there are Network problems.
Now what I would like to do is have all these local databases write to a central database say every minute but only write new records to the central database. The Central Database can either be Access or SQL.
I have started working on an existing MS access application (Front-end & Back-end) built with VBA. The frontend linked to a back-end access database which contains only tables. Because of the nature of the work involving vehicles. Each user has their own copy of identical databases with their individual data stored in backend.
Right now we just copy each back-end database via ftp and store it as usr_backenddb_date.accdb Back end databases contain around 16 tables with most of them containing a autonumber field as a primary key. Further tables are connected with each other referencing the primary key as a foreign key .
I would like ideally create a function in vba that lets me select the database and merge all the data from that database to a identical central database. For the initial part, I am thinking of combining each individual table in each database and changing the autonumber field with SQL with possibly trying to cascade the change so the refrences remain intact.
Constant Access to one back end db via internet (or server) is not really possible as the users are able to connect to internet (network) only few times a day. There are over 100 backends (for each vehicle) with over 1000 records in each.
I have a problem and wander if anyone knows a solution?
I have a central database that runs on a stand alone machine (soon to run on a server with VPN connection), it has a form that enables us to report faults this works fine however i would like to be able to send the form out to our operators (all over the world) for them to fill in when needed. then what we would like is for them to send it back to usand then for us to upload it to the central database.
I'm new to Access and VBA, for the record. What I need to do is copy a SQL database (table structures and records) and save it as a local Access database, so that the user can query and make any changes on the local file without editting the SQL database.
Looking around, it seems like the best option would be to use the TransferDatabase method with the acImport option. Does this sound reasonable, or can someone with more experience suggest a better way to go about it? Thanks.
i have done a database for a library that enables the user to enter the information about the books and search for it, the administrator have the right to enter the info about the books and edit and search the others have more limited rights depending on which group they belong, i used for the connection (currentProject) because i did all the forms and tables in one DB a friend from this forum told me and i think he s right, that if i want to put it on the network i have to separate the forms and the tables ie save the tables and the queries on another database that i put on the network i have a problem 1- im not sure how to connect to another database can i do it like this Dim conDB as ADODB.connection set conDB=new ADODB.connection conDB=openDatabase("the path") or not 2-i have done the result form with a record source (search-query) and then inserted the fields into the form in the design view can i do that with a query if it was from another database or i need to write it all in code (taking into consideration that i havent found how to insert datacontrols and do the binding, because im using french access, that s why i assigned automatically a record source for the form) thanks in advance
I have a database that the table is located on a sharepoint website. This worked out well in the beginning but now that the database has thousands of records, it has slowed up a bit. What I want to do is at the load of the the database, it will automatically copy Sharepoint table to a local table located on the computer to speed up querys and such.
I'll try to explain what I'm trying to do. I want to use the MS Access (2010) application residing on the remote server on a Access database(accdb) that is located on my local (home) PC. I copied the database from the server. How do I get Access (running on the server) to work on my copy of the database?
I tried using my local (on my PC) copy of MS Access (2007) to work on the database, but but I get file errors (file paths look like server paths).
The front end has been saved in a accde format and distributed to 20+ users. They have all saved this onto there computers and this is used as an application.The users complete a form on a weekly basis on the application and then save. This then saves into the back end.For some reason I have noticed that a few users submission randomly do not save. For the life of me I cannot understand why.All users have saved them into the back end in the past but every now and then submission for users do not save.
I am adding a exisiting field which is already available on a Global Table and would like to add it on a local table within the same database. Also bearing in mind the db contains main objects - Tables/Queries/Sharepoint lists/forms/reports
Whats the process in doing this? Once added how does the data get populated?
I have been working on a database for over a month now, and my boss just threw a monkey wrench in my work. I believed that the Metal Market Prices would be entered once a week in the current DB. My boss informs me today that he wants an employee to go in every morning and enter that days Price for Each Metal with respect to many different markets.
There is a total of 12 metals, and 5 markets. I need the data to be stored first by date, then by either market or metal, and lastly by which ever isn't used second (Either: date-market-metal, or date-metal-market). I think the Latter of the two methods makes the most sense. Is it possible for my current DB to lookup values from the Metals Database based on date-metal-market?
Is there a good replacement for the use of local forms in an Access database? We have a large A97 replicated database application that we are trying to update. We use local forms for data output. Since these are no longer available we are looking for a workaround.
Hi Everyone! I am a huge novice in database programming. I have a very basic database that I put together. I understand how to retrieve records and such, but I need to be able to perform mathmatic calculations in queries. I either need a new database written, or the one that I have ''tweeked''. I have no clue what the going rate is, but i am willing to pay. Just let me know what your cost is. Contact me @ davemayer@bellsouth.net or if you would like on the forum. Once you contact me I will give you my phone number and we can discuss the specifics of the database and whatever $ you will need to do the job. I am in a bit of a hurry to get this done, so let me know ASAP. Thanks a million. Dave.
I've set up a database for product tracking. It is going to be used by several users at one time. I'm going to implement it in stages as inevitably there is going to be some fine tuning to be done etc. I have a few questions with regards to editing.
1, I presume that I cant alter the database while it is being accessed by others?
2, Is it better to alter another copy of the database and the import the data being generated and then copy it to back the server in one go?
3, If so how do i do this as I’ve had a trial go and failed miserably?
4, Is there another way of doing this minimising the down time of the database?
Being relatively new to access your help would be gratefully appreciated.
I have a database which i am constantly updating and improving. I have a few friends who use the database in a current form. If i made an update say to a form or to some of the coding to make the DB more efficent, how can i update the dBs my friends are using without damaging or changing the existing data, or migrating it to the new design.
Hi I am having problems updating my database using a dropdown menu can someone see where I have gone wrong: Error: ADODB.Recordset error '800a0bcd' Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record. /update_style.asp, line 34 ************************************************** ******* drop down form: Code:<% 'Dimension variables Dim styConn 'Holds the Database Connection Object Dim rsstyle 'Holds the recordset for the records in the database Dim strstyke 'Holds the SQL query for the database Dim lngRecordNo2 'Holds the record number to be updated 'Read in the record number to be updated lngRecordNo2 = Request.QueryString("RemID") 'Create an ADO connection object Set styConn = Server.CreateObject("ADODB.Connection") styconn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=ny database connection is here that's not the problem;" 'Create an ADO recordset object Set rsstyle = Server.CreateObject("ADODB.Recordset") 'Initialise the strSQL variable with an SQL statement to query the database strstyle = "SELECT memorial.pagestyle FROM memorial where remid=" & lngRecordNo2 'Open the recordset with the SQL query rsstyle.Open strstyle, styConn %> <form name="form" method="post" action="update_style.asp?remid=<%=lngRecordNo2%>"> <tr> <td width="50%"> <select size="5" name="pagestyle" style="width: 95%;" style="border:1px outset #5286A5; " style="border:1px outset #5286A5; " multiple style="border: 1px outset #5286A5"> <option value="Bible">Bible</option> <option value="boat">boat</option> <option value="candles">candles</option> <option value="child1">child1</option> <option value="child2">child2</option> <option value="cross">cross</option> <option value="diary">diary</option> <option value="footprints">footprints</option> <option value="heaven1">heaven1</option> <option value="landscape">landscape</option> <option value="music">music</option> <option value="ocean">ocean</option> <option value="ponga">ponga</option> <option value="rose">Rose</option> <option value="rose2">Rose2</option> <option value="sky">Sky</option> <option value="toitoi">Toitoi</option> <option value="valley">Valley</option> </select></td> <td width="50%"> <font face="Arial" size="2" color="#5286A5"> Click here to view what each style template looks like.</font></td> </tr> <tr> <td width="100%" colspan="2" align="center"> <input type="submit" value="Submit New Style" name="style" style="color: #FFFFFF; font-weight: bold; border: 1px solid #5286A5; background-color: #5286A5"></td> </tr> </form> <% 'Reset server objects rsstyle.Close Set rsstyle = Nothing Set styCon = Nothing %> ************************************************** * The scripted page after submission (where the error accures): <!-- #INCLUDE FILE="connection.asp" --> <% 'Dimension variables Dim adoCon 'Holds the Database Connection Object Dim rsUpdateEntry 'Holds the recordset for the record to be updated Dim strSQL 'Holds the SQL query to query the database Dim lngRecordNo 'Holds the record number to be updated 'Read in the record number to be updated lngRecordNo = CLng(Request.Form("Remid")) 'Create an ADO connection object Set adoCon = Server.CreateObject("ADODB.Connection") 'Set an active connection to the Connection object using DSN connection adoCon.Open Connect_String 'Create an ADO recordset object Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset") 'Initialise the strSQL variable with an SQL statement to query the database strSQL = "SELECT memorial.pagestyle FROM memorial WHERE remid=" & lngRecordNo 'Set the cursor type we are using so we can navigate through the recordset rsUpdateEntry.CursorType = 2 'Set the lock type so that the record is locked by ADO when it is updated rsUpdateEntry.LockType = 3 'Open the recordset with the SQL query rsUpdateEntry.Open strSQL, adoCon 'Update the record in the recordset rsUpdateEntry.Fields ("pagestyle") = Request.Form ("pagestyle") 'Write the updated recordset to the database rsUpdateEntry.Update DIM strCustomerID strCustomerID = rsupdateentry("remID") 'Reset server objects rsUpdateEntry.Close Set rsUpdateEntry = Nothing Set adoCon = Nothing 'Return to the update select page in case another record needs deleting 'Response.Redirect "update_select.asp" Response.Redirect ("editmemorial.asp?REMID=") & strcustomerid %>
I have a small form that when people fill in it should update their details intomy database. However the page seems to be working fine and completes corerectly. But when i go to the table in the database it just enters a blank line fo reach field.
A client has had a database in operation for about a year. They requested some updates. I have been trying to figure out an easy way to update table structer with out having the client go into the backend and add a field or two.
I wrote an update program, that copies the table from their backend, and copied it to the updated table and tried to copy it back to thier backend, but since it is in a relationship, it won't let me.
Hi, im currently working on a database which is for someone else. Whe it is handed over to them, they will no doubt want some changes done every so oftern eg new reports, changes to forms etc.
What is the best way to do this? The database holds a lot of data, so I dont think its feasible for them to send it to me via email everytime they need something changed!
Ive noticed a few posts here about splitting the database into a front/back end. If this was done, would they just have to send me the front end; this would probably be a smaller file ye?
If this is going to be the best way round the problem; how easy is it to split a database which is basically already made?
Hi, im currently working on a database which is for someone else. Whe it is handed over to them, they will no doubt want some changes done every so oftern eg new reports, changes to forms etc.
What is the best way to do this? The database holds a lot of data, so I dont think its feasible for them to send it to me via email everytime they need something changed!
Ive noticed a few posts here about splitting the database into a front/back end. If this was done, would they just have to send me the front end; this would probably be a smaller file ye?
If this is going to be the best way round the problem; how easy is it to split a database which is basically already made?
I have a number of Yes/No fields in my database (their default value is False), and I am looking to update these via one of my webpages via INSERT INTO... statement.
What I have is a checkbox on the page, with checked value as true, and then before the values are written to the database, for the unchecked boxes, it converts all empty check box values (ie the ones which have been left unticked) to false, so the INSERT INTO statement will either write true or false for each checkbox, like this... true,true,false,true
The thing is, on executing the code, it add all the other details fine, and hits no errors, but NONE of the checkboxes get updated... these ALL remain unticked in the database, so ignoring any true values which are written to it. Please can someone help, I would be sooo thankful ).
Beginner at ASP VBscript. Hi I'm trying to update a Access database through a form using ASP VBScript. Kind of lost. I've looked at some sites and they all have complex ways of doing it. CAn anyone help with a simple little example. say a table with firstname and second name.
I'm having difficulties creating a sql statement which updates data in another Access database from a current database.
I'm able to do an insert and delete statement just perfectly, but not an Update. :confused:
For example, here is a dummy sample (which does not work):
Update tbl_test1 IN '\C:Tempabc.mdb', tbl_test2 set tbl_test1.Name = tbl_test2.Name Where tbl_test1.ID = tbl_test2.ID
I tried a couple of variations, however, I keep getting an error. Also, I don't want to create a database link (due to the sheer complexity of my project, which I'll spare you the details..)
Does anyone know how to do this? I searched the forums and came up with zilch! =(.. Help??!
I have been asked to add features (and fields) to an existing database containing private medical data. The idea is to add features to the empty database, test it with fake data, purge the fake data and deliver it to the user in another state with a simple update button. The Access 2010 database is in one piece (No front/back end).
I am looking for the best/shortest VBA method to move a lot of preexisting field data from the old database to the new version. I think I have it mostly figured out; I have code for counting, reading and writing the table names, field names and field data.
However, because the data types vary within the tables and there will be new fields in the new versions tables, my only idea, so far, is to code through the data table-by-table and field-by-field, a lengthy process with a large footprint. Moving entire tables or complete records may not be effective. Any faster way to transfer all these tables field data?
