Results 1 to 9 of 9

Thread: Connection string to MS access

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2017
    Posts
    5

    Connection string to MS access

    Hi All,

    I have an old Visual basic application that is written by someone else. I have the following connection string defined in the code. Foe the following connection string, I was told that i need to install Office access 2003 on my computer and then only the code will work. Is there any way, I can upgrade the following string so instead of office access 2003, i will start using office access 2010.

    Code:
    Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + gDBPath + ";Persist Security Info=False

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Connection string to MS access

    I think that may be the first time I have saw "Access Driver" in a connection string

    Normally it would be the Jet driver for mdb files and the ACE driver for accdb files.
    I personally do not work with accdb files at all. I either use mdb or SQL Server.

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Connection string to MS access

    ACE is the private modified version of Jet 4.0 that the Office Team took into a corner and hacked on. It will never be part of Windows, it is their own thang.

    ACE = "ACcess Engine" or something like that.

  4. #4
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    19,541

    Re: Connection string to MS access

    Maybe connectionstrings.com ?

    The ACE drivers can be downloaded from microsoft. Is that what you need? See the above link for examples of connection strings using ACE & mdb/accdb
    2010: https://www.microsoft.com/en-us/down....aspx?id=13255
    2016: https://www.microsoft.com/en-us/down....aspx?id=54920
    Last edited by LaVolpe; Jul 24th, 2017 at 08:01 PM.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  5. #5
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: Connection string to MS access

    Something is fishy there because that particular ODBC connection string would not work with Office 2003's Desktop Drivers. Instead it looks like it is trying to use one of the ODBC Desktop Drivers installed by Office 2007 or later.

    So you either have things pretty distorted after trying to slap it around or you aren't telling us the real story.

    In any case if the program was thunking through ODBC one can only cringe at how bad the rest of the code must be. Are you sure it is worth saving? The original programmer seems pretty clueless, some sort of copy/paste cowboy.

  6. #6
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Connection string to MS access

    This is a function I use quite often (I have it in a Module all by itself):

    Code:
    Public Function dbConnect()
        Set cnn = New ADODB.Connection
        With cnn
            '.Provider = "Microsoft.Jet.OLEDB.4.0" 'Connecting to an MS Access 2003 (& earlier?) database
            '.ConnectionString = "User ID=Admin;password= ;" & " Data Source=" & App.Path & "\dbname.mdb;"
            .Provider = "Microsoft.ACE.OLEDB.12.0"  'Connecting to an MS ACCESS 2007-2013 database
            .ConnectionString = "User ID=Admin;password= ;" & " Data Source=" & App.Path & "\dbname.accdb;"
            .CursorLocation = adUseClient
            .Open
        End With
    End Function
    Some will say App.Path is not a good practice...and that's okay...just put your database path in the connection string.
    Also 'cnn' might be a global or local declaration...your choice...just make sure it is useable where you need it.

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Connection string to MS access

    I generally create a string variable to hold my connection string and load the value from an ini file.
    I would strongly recommend this or similar method for all projects. Never hard code the connection string and never hard code the path even if it is app.path

    It is very simple to allow the connection string to be soft and read from a file and that allows the end user to store the database at a location of their choosing.

  8. #8
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    9,145

    Re: Connection string to MS access

    Yes, DM, my practices may not be the best, but FOR ME, they work (I don't have others running my programs...only me). The point of the code was to differentiate the Provider difference between the earlier and later versions of Access, that's all.

    Many other can tear apart my code as well, but I just wanted to tell OP how "I" connect to those different Access versions. I use app.path as I am the only user...it's 'convenient'.

    Sammi

  9. #9
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Connection string to MS access

    Understood, and there is no issue with using app.path for your own usage so long as you understand the drawbacks.
    If any software is to be released it should always use a configurable connection for data access.

    I do a lot of stuff that only runs locally as well, many times it will only be used once and those almost always get hard coded simply because it is faster and does the job for me but if one wants to create apps for others it is good to develop the habits of using soft configuration methods and proper data folders rather than using app.path.

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width