Results 1 to 9 of 9

Thread: How to connect access 2003 DB dynamically at run time

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    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...

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: How to connect access 2003 DB dynamically at run time

    Quote Originally Posted by jmcilhinney View Post
    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)

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    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

  5. #5
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,245

    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!

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: How to connect access 2003 DB dynamically at run time

    Quote Originally Posted by Shaggy Hiker View Post
    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

  7. #7
    Member Surka's Avatar
    Join Date
    Dec 2013
    Location
    Where you live
    Posts
    34

    Re: How to connect access 2003 DB dynamically at run time

    Use notepad

  8. #8
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    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

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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
  •  



Click Here to Expand Forum to Full Width