Storing and decrypting the Server connection string located outside the Access database

[anasser]

I have a 2002 Access database that connects to a 2000 SQL Server. The
connection string for the server that holds all the connection
information is in the Access database now. I need to make this
connection string stored in a Binary file on a specific location on the
intranet network, so whenever the user wants to upload or download
their data from the SQL Server the Access database should go to the
network, get the BIN file, decrypt it, and get the connection string to
the Server from it to use for the upload/download of data for that
Database.

The connection string has to be stored in a bin file on the network for
Company security purposes, so it can be changed incase the Server
access security or name change. By doing this, it will minimize the
impact on the Access Database users.

Help on this issue is appreciated.
Thanks.

 

[Albert D.Kallal]

You don't even need to use a bin file,..but just a plain Jane text file
would do...
You just open it, and read in the connection stuff...

I assume you are using a DSN-less connection, so your code can link to sql
server?

You can find that code here:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

As to if you "copy" the text file, email it, or open it on some know network
location, that is up to you....

 

[anasser]

Albert, Thanks for the prompt response...
I have to encrypt the connection string (in a bin file format) and have
MS Access go and read it from some specific location on the network for
many reasons, company policy and request, and so that the users wont
have to do anything from their end in case anything in the connection
string changes and also to secure it from everybody.

 

[Albert D.Kallal]

Albert, Thanks for the prompt response...
I have to encrypt the connection string (in a bin file format) and have
MS Access go and read it from some specific location on the network for
many reasons, company policy and request, and so that the users wont
have to do anything from their end in case anything in the connection
string changes and also to secure it from everybody.

Ok.....you can still use the "legacy" commands "open" to read that bin
file......

So, using the DNS less code + some code to open the "bin" file and read the
contents should be just fine....

I am not sure actually now what your problem is. You can read that bin file,
and use that sample DSN-less code?

Do you need some sample code to read a bin file???

 

[anasser]

Yes I was not able to get vba code to open binary files. do you have
samples like that.

Thanks

 

[Albert D.Kallal]

As I mentioned, you should make it a text file. You can still encode the
data as hex, or whatever, but you should still be albe to open it with a
text file editor, or notepad to read it.

You can also give the file name a diffetn extension, and that tends to hlep
"hide" things also.

I often name by license key file with .dll

somting like:

rdata.dll

Note the extension of .dll, so users don't try end oopen it...
(anyway, just a idea)

The "legacy" code that goes back 20 years to the origal IBM pc basic still
works in ms-access.

The code to open and read a text file is:

Dim sData As String
Dim intFile As Integer
Dim strFile As String
Dim strOneLine As String

strFile = "c:\t.txt"
intFile = FreeFile

Open strFile For Input As #intFile

Do While EOF(intFile) = False
Line Input #1, strOneLine
' ...do whatever
'Debug.Print strOneLine
Loop

Close intFile


So, encode/encrypt your data into hex values as ASCII, and use the above to
read in the file....

You can also read byte wise binary data, but the above use of "line input"
grabs a whole line from the file up to the first vbCrlf

If you need to "read" and include characters like linefeeds etc. in the
data..then you can go

Dim databuf As String
Dim lngChars As Long

strFile = "c:\t.txt"
intFile = FreeFile

Open strFile For Binary As #intFile

' get lenght of file data
lngChars = LOF(intFile)

databuf = Input(lngChars, intFile)

Close intFile

So, you can load up a text, or binary data file into databuf with the
above...

 

[anasser]

Thanks Albert, I will try this and hopefully I will get it to work for
the purpose required.

 

[anasser]

Thanks Albert, I will try the code above and let you know if there are
still any more issues