-
Feb 21st, 2018, 09:50 AM
#1
Thread Starter
Junior Member
[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.
-
Feb 21st, 2018, 02:17 PM
#2
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?
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...
-
Feb 21st, 2018, 03:02 PM
#3
Thread Starter
Junior Member
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.
-
Feb 21st, 2018, 05:03 PM
#4
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.
-
Feb 22nd, 2018, 07:18 PM
#5
Thread Starter
Junior Member
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
-
Feb 22nd, 2018, 07:45 PM
#6
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:
Dim [call] = GetCurrentCall()
Dim folderPath = "C:\RRLogger Data"
Dim fileName = $"County Hunter - {[call]}.accdb"
Dim filePath = Path.Combine(folderPath, fileName)
Dim builder As New OleDbConnectionStringBuilder
builder.Provider = "Microsoft.Ace.OLEDB.12.0"
builder.DataSource = filePath
Using connection As New OleDbConnection(builder.ConnectionString)
'...
End Using
-
Feb 22nd, 2018, 07:50 PM
#7
Re: [RESOLVED] Building a ACCESS connection string with code in vbs 2015
Note that this is string interpolation:
vb.net Code:
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:
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|