reading text file line by line and puting it into string variables (1 Viewer)

[wind20mph]

hi again everyone. i didn't post quite a long time. been doing great with ms access 2007. and moving on, i need help on reading text file, line by line and storing them into separate string variables...

i have this text file: "E:\Data\Access\connection.dll" and it contains 4 rows:

102.16.21.14
mntdb
mntuser
123456

which means, instead of editing the module connection string, i'll just edit the text file to use which server.

i tried the following code but it didn't worked:

Dim TextFile As Integer
Dim FilePath As String
Dim FileContent, strHost, strDB, strUser, strPassword As String

'File Path of Text File
  FilePath = "E:\Data\Access\connection.dll"

'Determine the next file number available for use by the FileOpen function
  TextFile = FreeFile

'Open the text file
  Open FilePath For Input As TextFile

'Store file content inside a variable
  FileContent = Input(LOF(TextFile), TextFile)

'Report Out Text File Contents
  strHost FileContent
  strDB FileContent
  strUser FileContent
  strPassword FileContent

'Close Text File

  Close TextFile

i wanted to use it in a connection string function module. any help, assistance is highly appreciated.

Function conString() as String

   conString = "Driver={MySQL ODBC 3.51 Driver};Server=" & strHost & "Port=3306; Database=" & strDB & ";User=" & strUser & ";Password=" & strPassword & ";Option=3;"  

End Function

i will be using this connection string in all of my modules. any help please....

 

[Frothingslosh]

Two things:

First off, are you aware that you're declaring FileContent, strHost, strDB, and strUser as variants and not strings? You need the AS VarType after EVERY variable name unless you want them to be variant, since that's the Access default.

Second, saving login info, *ESPECIALLY* passwords, in a text file is a terrible, terrible idea, even when the text file is cleverly disguised as a DLL. Normally, I would either store hashed passwords in an appropriate table and/or have them enter the password, check it for any funny business (google 'xkcd tommy tables'), compare it against the saved one if appropriate, and concatenate it into the connection string.

Of course, it's always VISIBLE in the connection string, so I normally use Trusted Authentication backed by a Users table instead, but that may not be an option for you.

So what precisely is the purpose here, other than logging into a specific server? Why are you using a text file instead of storing it in a table (either front-end or back-end)?

As to your specific question, what precisely is meant by "i tried the following code but it didn't worked:"? That's a lot like going to a mechanic and saying 'my car isn't running right' but not telling him or her anything else.

 

[wind20mph]

First off, thank you Frothingslosh for the prompt reply. My purpose in storing it in a text file is like those that I developed in VB.net that whenever they change server, I only replace the text file and not the code.

The second purpose is that, we are in a place where DHCP is a common source of IP (Not my concern really, its is the network admmin), that whenever the IP refreshes, I have to change the text file instead of reopening the codes that are all in 117 workstations.

With regards to security, it is not about the MySQL password as it only have access to tables from the database and that is least interesting from end-users. It is not an online or public connection, the IP might look public but trust me, its local connection that only the network admin can do the link.

I was just making it a pattern for a lot of previous development from vb.net because honestly, I love the microsoft access reporting style (clean and precise).

Below is the code in my vb.net:

Function conSQL() As String
        Dim stPath As String
        stPath = Application.StartupPath & "\dom.hbk"
        Dim stServer As String, stPort As String, stDB As String, stUser As String, stPass As String
        Dim flieData As StreamReader = New StreamReader(stPath)

        stServer = flieData.ReadLine
        stPort = flieData.ReadLine
        stDB = flieData.ReadLine
        stUser = flieData.ReadLine
        stPass = flieData.ReadLine

        conSQL = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" & stServer & ";DATABASE=" & stDB & ";UID=" & stUser & "; PWD=" & stPass & "; OPTION=3;"
        flieData.Close()
        flieData = Nothing
    End Function

 

[Cronk]

Your vb.net is reading the text file line by line, whereas your vba is reading the entire file into a string.

Lookup up vba references to
Line Input #1

 

[wind20mph]

Yes Cronk I need help on how to do it line by line.

 

[Frothingslosh]

It will include either Chr(10) or Chr(13) as line breaks. The easiest thing to do is to use the Split function to input the text into an array. Make a note (for future reference) that element 0 is IP address, 1 is the database, 2 is the login, and 3 is the password, and then concatenate the appropriate elements into the appropriate locations in your connection string.

 

[wind20mph]

thanks again Frothingslosh but still i didn't quite get it. i have searched for line by line variable for ms access vba but unfortunately, i didn't catch or find any sample codes.

This is a sample i got so far:

Dim strFilename As String: strFilename = "E:\Data\ACCESS\dom.hbk" 
Dim strTextLine As String 
Dim iFile As Integer: iFile = FreeFile Open strFilename For Input As #iFile 

Do Until EOF(1)     
Line Input #1, strTextLine      
Loop 

Close #iFile

however, it only reserves the last line.

 

[Cronk]

Add the highlighted line to your loop.
Do Until EOF(1)
Line Input #1, strTextLine
debug.print strTextLine
Loop

That might lead you to try something like

Line Input #1, stServer
Line Input #1, stPort
etc