-1

My form is housing the data I need it to, and I have gotten all the functions corrected. I am trying to figure out a way to get the data from the local access table to auto upload to a sql server db as long as there is an established connection, I was told record sets may be a good way to go. And I tried just linking the table but that is not what my boss is looking for. ANy ideas or direction would be greatly appreciated.

The data housed is employee data, each time it uploads I just need it to push all data regardless if its new or not onto the same table, it will be overwritten. THe data now is saved to a local table whenever the form is filled out, so if it can connect it needs to upload and if it cannot then it just sits and continues to sit on the local table.

user2119980
  • 499
  • 4
  • 12
  • 25
  • You may have to describe your problem a bit more. Auto upload WHICH data, only what is newly inserted using your form? What about updates on existing data, allowed or not, local or on the server? What about when the access DB is NOT connected to your server? – Sascha Rambeaud Feb 28 '14 at 18:10
  • I made the appropriate edits – user2119980 Feb 28 '14 at 18:28
  • So you do **not** want a permanently open connection, or is that acceptable? I'm still not clear on what you want, and why a linked table isn't what your boss is looking for? Why does he not want a linked table? Does it really matter, or is there some business reason for it? – Johnny Bones Feb 28 '14 at 18:42
  • IT would be nice if there was a constant open connection, this is a just in case situation because the people that will be using it are out in the field at remote locations who may or may not always have connections. So if there isn't a connection the data still needs to be saved which is why I have it writing to a local table. – user2119980 Feb 28 '14 at 18:45
  • Did you investigate the option I suggested in my answer to your earlier question [here](http://stackoverflow.com/a/22008889/2144390)? – Gord Thompson Feb 28 '14 at 18:57
  • I posted a reply on the answer you gave me Gord – user2119980 Feb 28 '14 at 19:17

1 Answers1

1

You need to create the destination table on SQLserver and link it to your access solution as an ODBC connected table. Also create an insert query that writes from your local table to the ODBC table.

Now you simply need to trigger that insert query (for example CurrentDB.execute ) during a fitting event in your form (open, close, before_update etc.).

Sascha Rambeaud
  • 297
  • 1
  • 8
  • I tried that before but it was not what my supervisor was looking for. Because if they cannot connect at the time they are filling out the form and inputting the data it wont write anywhere since there is no connection. – user2119980 Feb 28 '14 at 18:41
  • You need to setup the FORM so, that it is writing to the local table. It's just some EVENT on the form where you execute the insert query (for example in an after_update() event) that writes from your local table to the ODBC table. – Sascha Rambeaud Feb 28 '14 at 19:05
  • The form is setup to write to the local table already. I need it load upon opening to the sql server – user2119980 Feb 28 '14 at 19:06
  • That's what you do by writing (via an INSERT query) from your local table to the ODBC table. Ideally in an event on the form, for example after_update. You can find those events in the properties of elements (for example input fields) of your form. – Sascha Rambeaud Feb 28 '14 at 19:14