08-18-11, 15:23 #1Registered User
- 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?
08-25-11, 16:45 #2Super Moderator
- Join Date
- Jun 2004
- Arizona, USA
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 statementCode:
Update Table1 Values ('A', 'B', 'C')Code:
Update Table1 (Field1, Field2, Field3) Values ('A', 'B', 'C')
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.
08-25-11, 17:56 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
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 installationI'd rather be riding on the Tiger 800 or the Norton