MSAccess vba to read a text file any advise?

[wind20mph]

Hi pleasant day to everyone. I am new to this forum and I need some advise in MS Access - Excel VBA.

Starting with MSAccess, how do I read a text file line by line and put it in a VBA variable?

thanks in advance.

Data content of a text file
hostname = 192.168.33.11
port = 3306
username = admindb
password = adminpass1234
database = payrolldb

My purpose is to not modify vba code of Access everytime there is a new MySQL server. But rather just modify the text file for the connection.

 

[taurean]

Welcome to MrExcel forums!

I am not MS Access user but I am answering from VBA user point of view. Contents of text file.

[B]do not edit sequence of parameters!
[/B]hostname = 192.168.33.11
port = 3306
username = admindb
password = adminpass1234
database = payrolldb

Code to read contents into variable

Public Sub ReadSettingsfromFile()Const strSettingFile As String = "C:\Temp\Settings.txt" 'Change this path to suit.
Dim varContent
Dim intFF As Integer: intFF = FreeFile()
Open strSettingFile For Input As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=intFF"]#intFF[/URL] 
varContent = Split(Input(LOF(intFF), intFF), vbCrLf)
Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=intFF"]#intFF[/URL] 


Dim strHostName As String
Dim strPortNumb As String
Dim strUserName As String
Dim strUserPswd As String
Dim strDatabase As String


strHostName = Trim(Split(varContent(1), "=")(1))
strPortNumb = Trim(Split(varContent(2), "=")(1))
strUserName = Trim(Split(varContent(3), "=")(1))
strUserPswd = Trim(Split(varContent(4), "=")(1))
strDatabase = Trim(Split(varContent(5), "=")(1))


MsgBox strHostName & vbCrLf & strPortNumb & vbCrLf & strUserName & vbCrLf & strUserPswd & vbCrLf & strDatabase
End Sub