Results 1 to 7 of 7

Thread: [RESOLVED] Building a ACCESS connection string with code in vbs 2015

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    26

    Resolved [RESOLVED] Building a ACCESS connection string with code in vbs 2015

    I am upgrading a Visual Basic 6 program. In this program there is a database " County Hunter - call.mdb" I have changed the database's used to accdb. The problem is....when a user install the program the database (CountyHuntersMaster.accdb, CountyHuntersCommon.accdb, and CountyHunter[Call].accdb are installed into a data folder.

    When the user opens program for first time the CountyHunter data base has (their Call) replace the word [call ] in the database title. I have been trying to find an example of how to get the Connection string to be correct for the change:

    Example CountyHunterCall.accdb is changed to CountyHunterK8EMS.accdb

    I need to be able to make this change pragmatically as each user has a different call.

    I am using Access 2016, and visual Studio 2015 to do this.

    Can anyone point me in the right direction.

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Building a ACCESS connection string with code in vbs 2015

    Isn't it a string.. ? and you replace "[call]" with the correct word ?

    What code do you have for creating the connection?

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    26

    Re: Building a ACCESS connection string with code in vbs 2015

    the code is the problem......if I hard code it like:

    con.ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0; Data Source= C:\RRLogger Data\County Hunter - K8EMS.accdb"

    It will connect fine,
    the problem is when I try having the program insert the ....K8EMS.. into the string.

    I tried concatenating the string and save it as conSting then

    con.ConnectionStrin = conString .....and several others and I always get some sort of error. So I have managed to confuse myself and can't seem to figure it out. Need help gettin on the right path.


    Here is the code in the sub:


    Dim ds As New DataSet
    Dim dt As New DataTable
    ds.Tables.Add(dt)
    Dim da As New OleDbDataAdapter
    Dim mcall = lblCall.Text
    Dim cal = mcall 'lblCall.Text
    ' Dim strFilePrefix = "County Hunter - "
    ' Dim strFileSuffix = ".accdb"
    Dim conString = "Provider=Microsoft.Ace.OLEDB.12.0; Data Source= C:\RRLogger Data\County Hunter - K8EMS.accdb"

    con.ConnectionString = conString

    con.Open()

    da = New OleDbDataAdapter("SELECT * FROM [QSO - Master] ORDER BY [QSO ID] Desc", con)

    da.Fill(dt)

    DataGridView1.DataSource = dt.DefaultView

    con.Close

    This code will open the table But if in the conString I change the K8EMS to & cal & I get an ivalid path error. So My question is ..How can you have the program place the call into the connect string where the call is already determined by the program.
    Last edited by MikeDelke; Feb 21st, 2018 at 04:14 PM.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Building a ACCESS connection string with code in vbs 2015

    It is best to use a variable for the entire file location (so you can easily check if it is correct), then append that to the connection string. Here is the 'simple' way to do it:
    Code:
    Dim mcall = lblCall.Text
    Dim strFilePrefix = "County Hunter - "
    Dim strFileSuffix = ".accdb"
    Dim databaseFile as String = "C:\RRLogger Data\" & strFilePrefix & mcall & strFileSuffix
    Dim conString = "Provider=Microsoft.Ace.OLEDB.12.0; Data Source= " & databaseFile
    ...or the same thing using fewer variables:
    Code:
    Dim databaseFile as String = "C:\RRLogger Data\County Hunter - " & lblCall.Text & ".accdb"
    Dim conString = "Provider=Microsoft.Ace.OLEDB.12.0; Data Source= " & databaseFile
    Note however that this assumes that the file already exists at the location specified, if the file isn't there (or there is a typo in the variable databaseFile) then you will get an error.
    Last edited by si_the_geek; Feb 21st, 2018 at 05:06 PM.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Feb 2018
    Posts
    26

    Re: Building a ACCESS connection string with code in vbs 2015

    Thank Si_The_Geek !

    I tried your suggestion and everything works fine. I studied it for a few minutes and I feel I understand it a lot better.

    Again....THANK YOU

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

    Re: [RESOLVED] Building a ACCESS connection string with code in vbs 2015

    If you're building a connection string in code then you should probably use a connection string builder. It's not necessary but it's there for a reason and makes building connection strings less error-prone. E.g.
    vb.net Code:
    1. Dim [call] = GetCurrentCall()
    2.  
    3. Dim folderPath = "C:\RRLogger Data"
    4. Dim fileName = $"County Hunter - {[call]}.accdb"
    5. Dim filePath = Path.Combine(folderPath, fileName)
    6. Dim builder As New OleDbConnectionStringBuilder
    7.  
    8. builder.Provider = "Microsoft.Ace.OLEDB.12.0"
    9. builder.DataSource = filePath
    10.  
    11. Using connection As New OleDbConnection(builder.ConnectionString)
    12.     '...
    13. End Using

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

    Re: [RESOLVED] Building a ACCESS connection string with code in vbs 2015

    Note that this is string interpolation:
    vb.net Code:
    1. Dim fileName = $"County Hunter - {[call]}.accdb"
    It's supported in VB 2015 and later. It's simpler than using String.Format, which would be required in earlier versions:
    vb.net Code:
    1. Dim fileName = String.Format("County Hunter - {0}.accdb", [call])
    Personally, I prefer both to using multiple & operators. I'll rarely use more than one & and pretty much never use more than two.

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