Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2011
    Posts
    8

    Unanswered: Upload data from local to remote database

    I've made a relatively simple Access database which is to be distributed to multiple users. These users have intermittent access to the internet so will be entering data over a period of a number of days, then when they get to a connection will want to upload their data to a remote database. The remote database is used for processing the data, presenting it on the web.

    Is there a way to code a button in Access which, when clicked, will simply transfer all the data to a remote database. The remote database can be Access, SQL Server or MySQL, whichever is easier...my guess is Access to Access makes the most sense.

    The users of the database are not computer saavy and so the process needs to be as simple as possible.

    I'm assuming if the local and remote databases are identical in structure this should be possible. Obviously the data would need to be appended and only adding the latest records each time they go to upload.

    Any info/advice about this would be greatly appreciated.

  2. #2
    Join Date
    May 2010
    Posts
    601
    You can set use mySQL or MS SQL server on a web site so that it can to be used as a back end by a remote Access front end or even VB app. With an Access front end you can create linked tables to the SQL server. Use could then use append queries to add the data.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Jan 2011
    Posts
    8
    Thanks for the reply - that's encouraging that it can be done.

    Do the linked tables need to be connected to the remote database at all times, or can the user enter data without a connection and then upload/append when they reach a connection?

    I guess I'll just have to try it out.

  4. #4
    Join Date
    May 2010
    Posts
    601
    I like to make two separate front ends for this. One front end for entering the data into the local Access back end. Another front end to update the data from the local Access backend to the SQL server.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Jan 2011
    Posts
    8
    Regarding updating the data from local Access backend to SQL Server, would you do this in VBA just using the standard connection string/recordset code with an append query specifying the relevant tables?

    Presumably I can write this code and just assign it to the click even of a button? Can I ask why you use 2 front ends?

    Also, is it necessary to have linked tables? I did a quick test and there doesn't seem to be a way to link tables via http/ftp, only over a network. Aren't linked tables always kept in sync?

    Thanks for the help - it's much appreciated.

  6. #6
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by stoyleg View Post
    Also, is it necessary to have linked tables? I did a quick test and there doesn't seem to be a way to link tables via http/ftp, only over a network.
    That is corrent. you do not use http/ftp since they go through a webserver/ftp server. You do not cuse the http or ftp protocals to connect to a SQL Server. AFAIK, neither http or FTP these protocals will can be used to connect to a remote SQL Server. YOU want ot use TCP/IP. You can use the internet with the TCP/IP protocal for a netwotrk connection toa remopte SQL Server. You need ot go through the Database driver on your computer. You can use a DNS or use a DNS-less connection to the SQL server using the correct installed database driver.

    What method(s) have you used to successfully connected an Access front end to a local SQL server?


    Quote Originally Posted by stoyleg View Post
    Aren't linked tables always kept in sync?
    <confused> In sync with what? </confused>
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  7. #7
    Join Date
    Jan 2011
    Posts
    8
    I'm actually thinking I'll use a MySQL server as the remote database as this makes life a little easier for various reasons. However, I guess the principle is the same as connecting to SQL Server just a different connection string. The method I've used, which appears to be working on my local system is as follows:

    Code:
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set con = New ADODB.Connection
    con.Open ("Provider=MSDASQL; DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=MyDatabase; UID=MyUsername; PASSWORD=MyPassword; OPTION=3")
    
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM MyTable", con, adLockOptimistic, adUseClient
    
    rs.AddNew
    rs("firstvalue") = Me![firstvalue]
    rs("secondvalue") = Me![secondvalue]
    rs.Update
    rs.Close
    This works well so I'd imagine all I need to do is expand that code so all the required values are transferred across to the remote database.

    In order to connect to a remote MySQL database I guess I'll need the IP address, but whoever is hosting it will need to allow remote access. I'd imagine this would be the same for SQL Server?

    Don't worry about the linked tables thing - I'm fairly sure I can do it without.

    However, I'd appreciate your opinion on the above.

    All the best

  8. #8
    Join Date
    May 2010
    Posts
    601
    Yes that is the idea.

    Note: When is use SQL Server this is generic reference. It could be MS SQL Server, mySQL server, Oracle, etc.

    In order to connect to a remote MySQL database I guess I'll need the IP address, but whoever is hosting it will need to allow remote access. I'd imagine this would be the same for SQL Server?
    That is correct. It would be true for any SQL Server. It is common that Web Site Hosting ISP's do lock down the ports for security reasons.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •