-
Dec 31st, 2013, 10:41 AM
#1
Thread Starter
Lively Member
How to connect access 2003 DB dynamically at run time
Hi,
I am doing a project in VB 2010 with back end access 2003, where i will retrieve, insert, update data in DB, currently i am using the connection string in my source like below mentioned before i do insert / update / Select.
Code:
Dim Con As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\MyDataBase.mdb;Jet OLEDB:Database Password=123456;")
I like to replace the connection string to access the DB where ever the DB locates (networks) which is user define and i gone through vbforums that this can be done by .ini or xml file but i don't know how to implements this.
Kindly advice...
Thanks...
-
Dec 31st, 2013, 11:01 AM
#2
Re: How to connect access 2003 DB dynamically at run time
Just don't put your connection string in your code. Go to the Settings page of the project properties and add it there. You can then access it in code via My.Settings. The user can then edit the connection string in the config file, which is human-readable XML. To specify that the database is in the program folder, use "|DataDirectory|" for the folder path.
-
Dec 31st, 2013, 12:06 PM
#3
Thread Starter
Lively Member
Re: How to connect access 2003 DB dynamically at run time
Originally Posted by jmcilhinney
Just don't put your connection string in your code. Go to the Settings page of the project properties and add it there. You can then access it in code via My.Settings. The user can then edit the connection string in the config file, which is human-readable XML. To specify that the database is in the program folder, use "|DataDirectory|" for the folder path.
Thanks jm...
I changed my connection settings what you advice please refer below...
Code:
Dim Con As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & My.Settings.DBPATH & "\SRSoftBuild.mdb;" & _
"Jet OLEDB:Database Password=SR123;")
But i am getting error at Con.Open()
"Format of the initialization string does not conform to the OLE DB specification."
And how do i set for the connection for DB which is located on networks (LAN)
-
Dec 31st, 2013, 12:13 PM
#4
Re: How to connect access 2003 DB dynamically at run time
Have you looked at what the connection string comes out to be with that code? I think that you might end up with somepath\\SRSoftBuild.mdb. In other words, the \ might be duplicated.
My usual boring signature: Nothing
-
Dec 31st, 2013, 12:16 PM
#5
Re: How to connect access 2003 DB dynamically at run time
Every Access connection string you could possibly need at ConnectionStrings.Com
As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"
Reviews: "dunfiddlin likes his DataTables" - jmcilhinney
Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!
-
Dec 31st, 2013, 12:55 PM
#6
Thread Starter
Lively Member
Re: How to connect access 2003 DB dynamically at run time
Originally Posted by Shaggy Hiker
Have you looked at what the connection string comes out to be with that code? I think that you might end up with somepath\\SRSoftBuild.mdb. In other words, the \ might be duplicated.
How do i read connection string using .ini File were database location keep on changes in LAN
Eg:
In .ini file below mentioned connection settings will be there
ServerName=HostName or Local IP (On Con.Open() - program will search for given host name to locate the DB)
DBName=ABC.mdb (DB NAME)
kindly advice
-
Dec 31st, 2013, 02:28 PM
#7
Member
Re: How to connect access 2003 DB dynamically at run time
-
Dec 31st, 2013, 06:07 PM
#8
Re: How to connect access 2003 DB dynamically at run time
So, the database is bouncing around? That makes it a bit annoying, to be sure. Can you let the user find it, or do you want to put the location in some kind of text file and edit the file whenever the DB moves?
My usual boring signature: Nothing
-
Dec 31st, 2013, 10:08 PM
#9
Re: How to connect access 2003 DB dynamically at run time
If the user needs to be able to select the location of the database then you should be using a connection string builder. You can put your development connection string in the the settings and then remove the whole Data Source attribute from the connection string in the config file. You can then set the Data Source in code like so:
Code:
Dim builder As New OleDbConnectionStringBuilder(My.Settings.AccessConnectionString)
builder.DataSource = dataFilePath
Dim connectionString = builder.ConnectionString
Where you get the file path from is up to you, e.g. OpenFileDialog.
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
|