Results 1 to 14 of 14
  1. #1
    Join Date
    May 2008
    Posts
    6

    Unanswered: Online database?

    Hi a question
    I am doing a work in VB6. And I need to connect a database. However, instead of having the database on my computer or network server I would like it to be online on a Server reachable over Internet. The reason that I have 50 users spread out

    So: Whish database to use?
    And: How do I do this?

    Regards
    Per

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The answer of how to do it lies in the connection string...
    See www.connectionstrings.com for some examples.
    George
    Home | Blog

  3. #3
    Join Date
    May 2008
    Posts
    6
    On connections I find:
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;

    However this is for a loocal server. I am looking for an online Access database.

    Have a nice day
    Per

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you have access on the same file system as the files that are running, e.g. ASP files then you just use the database path as you would on a local machine.

    I.e. that connection string should work.

    However, with carriers such as SQL Server, best practice states that the database and IIS (ASP) servers should be separate, hence the extra parameter for server name.
    George
    Home | Blog

  5. #5
    Join Date
    May 2008
    Posts
    6

    Online, not ASP

    Thanks, however:

    Its not for ASP or a home page. I want a softaware running on a local computer access the database online.
    Per

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    This won't work with Access. Access is a file based database. VB clients require low-level O/S file access to the datafile, which cannot happen on an internet or FTP server.

    Now, there are a couple of approaches that you could try.

    • You could possibly try setting up a VPN connection. However, with multiple users, over a connection which is both slower than a LAN connection, and much more non-deterministic, you have a greatly increased opportunity for corrupt data files.
    • I've seen software which maps FTP servers to local drives. I tried the trial version. It appears that it works by locking the remote Access file, copying the entire file to the client, and after the user is done, it copies the file back & unlocks it. Multiple users will be waiting on each other left and right.


    You would be much better off, IMO, in setting up a database SERVER, and allow remote access to the server by your clients. PostgreSQL or SQL Server Express would work well. You will need to be security conscious, though. Locate a copy of "The Database Hackers Handbook," and follow their recommendations for the server type you choose.

    Really, 50 users is stretching the envelope for Access in a LAN setting. Trying this when you're adding the additional overhead of TCP/IP, and adding the variability in timing of the internet, and you're just begging for database corruption.
    Lou
    使大吃一惊
    "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


  7. #7
    Join Date
    May 2008
    Posts
    6

    Files based on Internet

    OK thanks.
    I have been laborating with the idea like this:
    -Having ONE main ACCESS database on one computer
    -Extracting file contents from this database into text files.
    -Uploading the text files on Internet (in a coded format off course to avoid outsiders to understund whats there).
    -Other users download the same and there local database is updated this way.
    -When they want to update it creates a request file that is also sent via Internet and then through a function used on the main computer such requests ar integrated into the main database.

    However such a system is a bit complicated and will also have some problems to work perfect as many changes could be made on the same database by different users at the same time and how to coordinate that in the best way in the end ...

    Any comments on this idea?

    Per

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Yes. Instead of Access or text files, use a database server. Like PostgreSQL or SQL Server Express.
    Lou
    使大吃一惊
    "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


  9. #9
    Join Date
    May 2008
    Posts
    6
    Thanks for that.
    Now: I have been dealing with computers and software since "stone age" but the mothern versions of database is new to me. So, please tell me how I best can start up PostgreSQL or SQL Server Express.

    What do I need?
    Whish software to use? (VB6 is my main one)
    How do I make the local computer "talk" to the database server?

    Any links? Any suggestions to guide me so that I look in the right direction.

    Per

  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Sure. I use both.

    For PostgreSQL, visit http://www.postgreSQL.Org & download the latest version. (If windows, download the binary distribution, msi file) You can install it on a windows server, or a linux server. (many linux servers already have a distribution that's been customized for them).

    You can also install it on your desktop machine, which is a pretty good means of testing things out.

    You'll probably also want to separately download the windows ODBC drivers. (They're included in the above install, but, a separate msi file makes for an easier distribution for your front-end clients)

    When you get the server installed, you'll need to edit one of the configuration files to allow users to connect over the network, and/or over the internet, and HOW they'll be allowed to connect.

    Be sure to enable MD5 password hashing and SSL encryption when used over the internet.

    You'll have to open two ports in your firewall to allow remote access to the database. PostgreSQL default ports are 5432 (and 5433, I believe)

    You'll want to use a DSN-less connection from your app, I'm sure. It makes for easier client installs and maintenence.

    In a virtually identical manner as with Access, you connect to the database server, and issue SQL Statements, open recordsets, etc.

    PostgreSQL has much better security than does Access, but, you'll need to set it up properly. Never give internet users any sort of admin access to the database. In fact, it's often best to not give Internet users ANY direct read or write access to the tables at all. Instead, set up views with read-only access to the tables, and only grant the users read access to the views. Likewise, set up stored functions to add or edit data to the tables, grant the stored functions the write access, and give the users execute privileges to the stored functions. That way, inside the functions, you can add code to eliminate SQL injection.

    For starters, I would also recommend that you get a copy of PostgreSQL 8 for Windows, by Richard Blum. (ISBN 0-07-148562-7) It'll cover all the above, plus, it has good descriptions of the differences between Access and database servers, like PostgreSQL. And, as an extra plus, it has a chapter devoted to upgrading from Access to PostgreSQL.

    Since you're talking 50 users, I would recommend PostgreSQL over SQL Server Express. While SQL Server Express might handle your concurrent user base, and while you could upgrade SQL Server Express if it wouldn't easily handle that many users, the license wouldn't be cheap. Whereas PostgreSQL has none of the limitations that were placed on Express. 'Out of the box,' it will handle hundreds, or even thousands of users, and at no charge.

    There are quite a few bonuses that are available when using a true database server - things like stored functions, and triggers, which allow you to make the client less complex, and enforce business rules within the database, that you soon wonder how you got along without them. And, by moving queries to the server, you can end up with a much more responsive system than by being forced to process them within the client. Certainly, you can greatly reduce the network/internet bandwidth requirements.
    Last edited by loquin; 06-18-08 at 23:45.
    Lou
    使大吃一惊
    "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


  11. #11
    Join Date
    Oct 2005
    Posts
    178
    1. Upload your MS access to the web server or Hosting website
    2. create and name a DSN (ODBC) for your database in the server.
    3. Use that DSN name in your connection statement.
    Use ASP(Active server page) with VBScript if that's what you're using for your web-based data retrieval.
    Last edited by fredservillon; 06-20-08 at 14:54.

  12. #12
    Join Date
    Oct 2005
    Posts
    178
    If you are NOT using web-browser, create a system DSN(ODBC) in the computer where your database resides and use that DSN name for connection, or you can use a DSN-less connection statement.

  13. #13
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    No offense, but I would never use Access over the internet, with multiple concurrent users. Among other reasons, file corruption issues seem to be related to server/client timing issues. You're just begging for corrupted data files when making concurrent updates by more than a few users, when you consider that time delays are a virtual certainty over the internet.
    Lou
    使大吃一惊
    "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


  14. #14
    Join Date
    Oct 2005
    Posts
    178
    Quote Originally Posted by loquin
    No offense, but I would never use Access over the internet, with multiple concurrent users. Among other reasons, file corruption issues seem to be related to server/client timing issues. You're just begging for corrupted data files when making concurrent updates by more than a few users, when you consider that time delays are a virtual certainty over the internet.
    I know what you mean and that's just the nature of MS Access. But there's a work around it, a technique that I've used with multi concurrent users and successfully eliminated the "lock by user" message even with many users at the same time. The idea is to prevent any user from opening any table for a long time. Only open the tables when saving and immediately close them after the save or update command. To do that is to load field's data to variables ( you can use array variables) during editing, then you can close the tables. As mentioned, open only the tables in save command and close them.
    But it's always good to use the correct database for the purpose. But if you have no choice for immediate solution like what I had experience, I had to invest time designing another the front-end application for the old system when I took over the application maintenance. This front-end bought us time until we finally got the new desired SQL server-based system thus alleviating the users frustrations.

Posting Permissions

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