-
Jul 24th, 2017, 05:06 PM
#1
Thread Starter
New Member
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
-
Jul 24th, 2017, 06:29 PM
#2
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.
-
Jul 24th, 2017, 06:32 PM
#3
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.
-
Jul 24th, 2017, 07:52 PM
#4
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.
-
Jul 24th, 2017, 11:18 PM
#5
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.
-
Jul 25th, 2017, 08:22 AM
#6
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.
-
Jul 25th, 2017, 12:45 PM
#7
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.
-
Jul 25th, 2017, 04:39 PM
#8
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
-
Jul 25th, 2017, 09:24 PM
#9
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|