Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Benton, AR

    Unanswered: Advice:Link VB to Access db or Excel

    I have built a searchable database in Access. I want to share it with some of my buddies but they do not have Access. I thought I saw some where that I can write my VB program and it can still access the database in Access even though it is not installed on the computer. Is this possible and how? Also can the info in the database still be changed? OR would it just be easier to build my VB program and have it access Excel for the info?


  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    It depends.

    If your users are on a single LAN, you can easily create a front-end/back-end database. (keep the tables in a single, common database file, and distribute the front end, with mapped access to the back end tables, to any user that needs it. The key point to remember is that accessing an MDB file requires drive-letter access, or UNC naming over a LAN. (YOu can also map a drive letter to a LAN UNC name.)

    However, when you want to allow users to access the data file over the internet, though, you have problems. If you place the Access file in an FTP server, apps exist which will map a drive letter to the FTP location. However, unless the users have read-only access, a local copy of the file will be made at the client's end (behind the scenes), and the remote (ftp) file is locked, until the client user is done with the file, at which time any data updates are completed, and the ftp hosted file is unlocked. Multi-user, but only one at a time...

    If you are distributing an executable VB-based "front-end" and an MDB file containing the data to the users, go for it. As long as your VB program doesn't access the data by automating MS access, but instead use ADO or DAO to interface to the MDB file, the individual users won't need Access installed on their computers. And, if they are disconnected from the Access file, (not running the front-end executable) you could send them a copy of the data (MDB) file, they could replace their copy with the MDB file with the updated copy and go. (assuming that you have made no changes to the table formats which will cause their front-end to not be able to work properly.

    It's important to always use full SQL expressions to update, rather than bypassing the field definitions.


    Rather than a SQL statement
    Update Table1 Values ('A', 'B', 'C')
    which requires that there be exactly three fields in Table1, instead use
    Update Table1 (Field1, Field2, Field3) Values ('A', 'B', 'C')
    This way, even if you later add a 4th and 4th optional field to the table, the SQL code won't bomb.

    All bets are off if your "VB code" is actually VBA running within Access. IN this case, you WILL need Access in order to intrepret your VBA code, and the users will need a copy of access on their computers. (Note - I believe the developer version of Office allowed you to create copies of your database that could be opened without a copy of Access on the client computer.

    If you can, avoid using Excel as the 'database.' Excel makes a very poor database. (Even though you CAN interface to an excel file, using JET and ADO.)
    Last edited by loquin; 08-25-11 at 16:50.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if you write an application using an Access backend (in reality a JET database) its licence / royalty free. the only possible complication is if the user uses a non microsoft platform. providing you supply the means to setup and maintain the data then there is no reason why storing data in a MDB should be a problem.

    IIRC the JET driver is part of a standard windows installation
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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