ODBC connection via a text file

J

Jac Tremblay

Hi,
I want to place the information for the connection in a text file and use
that file in the code to create an ODBC query. When I put the connection
information directly in the code, the query runs fine but I can't get it to
work when it is in a text file.
Can someone point out the problem. Here is the code.
' ***********************************************************
Sub ConnectionViaTextFile()
Dim wbkConnection As Workbook
Dim shtConnection As Worksheet
Dim rngConnection As Range
Dim strWbkPath As String
Dim strConnection As String
Const cstrTextFile As String = "ConnectionText.txt"
Const cstrCommandText As String = _
"SELECT TRET_PREV_ASRA.PROG_ASSU, TRET_PREV_ASRA.PROD_ASSU, " & _
"TRET_PREV_ASRA.AN_ASSU, TRET_PREV_ASRA.D_DEB_ASSU, " & _
"TRET_PREV_ASRA.D_FIN_ASSU, TRET_PREV_ASRA.REVE_STAB, " & _
"TRET_PREV_ASRA.PRIX_MARC, TRET_PREV_ASRA.TAUX_COTI_UNIT, " & _
"TRET_PREV_ASRA.TAUX_COTI_PLAN_CONJ, TRET_PREV_ASRA.UNIT_COTI, " & _
"TRET_PREV_ASRA.UNIT_COMP, TRET_PREV_ASRA.TIMB_MAJ, " & _
"TRET_PREV_ASRA.USAG_MAJ" & vbCrLf & _
"FROM " & _
"""OPS$DEVLIB01""" & _
".TRET_PREV_ASRA TRET_PREV_ASRA" & vbCrLf & _
"ORDER BY TRET_PREV_ASRA.PROG_ASSU, TRET_PREV_ASRA.PROD_ASSU, " & _
"TRET_PREV_ASRA.AN_ASSU"
Set wbkConnection = ActiveWorkbook
Set shtConnection = wbkConnection.Sheets(1)
Set rngConnection = shtConnection.Range("A1")
strWbkPath = wbkConnection.Path
If Right(strWbkPath, 1) <> "\" Then
strWbkPath = strWbkPath & "\"
End If
strConnection = """TEXT;" & strWbkPath & cstrTextFile & """"
MsgBox strConnection
With shtConnection.QueryTables.Add( _
Connection:=strConnection, _
Destination:=rngConnection, _
Sql:=cstrCommandText)
.Name = "QueryTest"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileTabDelimiter = True
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With
Set rngConnection = Nothing
Set shtConnection = Nothing
Set wbkConnection = Nothing
End Sub
' ***********************************************************
The code bugs on
With shtConnection.QueryTables.Add( _
Connection:=strConnection, _
Destination:=rngConnection, _
Sql:=cstrCommandText)
I use Excel 2000 and Windows XP Pro.
Thanks
 
J

Joel

You have to open the file and read it, then pass the read data to the
connection like below.

Sub ConnectionViaTextFile()
Dim wbkConnection As Workbook
Dim shtConnection As Worksheet
Dim rngConnection As Range
Dim strWbkPath As String
Dim strConnection As String
Const cstrTextFile As String = "ConnectionText.txt"
Const cstrCommandText As String = _
"SELECT TRET_PREV_ASRA.PROG_ASSU, TRET_PREV_ASRA.PROD_ASSU, " & _
"TRET_PREV_ASRA.AN_ASSU, TRET_PREV_ASRA.D_DEB_ASSU, " & _
"TRET_PREV_ASRA.D_FIN_ASSU, TRET_PREV_ASRA.REVE_STAB, " & _
"TRET_PREV_ASRA.PRIX_MARC, TRET_PREV_ASRA.TAUX_COTI_UNIT, " & _
"TRET_PREV_ASRA.TAUX_COTI_PLAN_CONJ, TRET_PREV_ASRA.UNIT_COTI, " & _
"TRET_PREV_ASRA.UNIT_COMP, TRET_PREV_ASRA.TIMB_MAJ, " & _
"TRET_PREV_ASRA.USAG_MAJ" & vbCrLf & _
"FROM " & _
"""OPS$DEVLIB01""" & _
".TRET_PREV_ASRA TRET_PREV_ASRA" & vbCrLf & _
"ORDER BY TRET_PREV_ASRA.PROG_ASSU, TRET_PREV_ASRA.PROD_ASSU, " & _
"TRET_PREV_ASRA.AN_ASSU"

Set fsread = CreateObject("Scripting.FileSystemObject")
Set fread = fsread.GetFile(strWbkPath & cstrTextFile)
SQLSTR = fread.readall
fread.Close

Set wbkConnection = ActiveWorkbook
Set shtConnection = wbkConnection.Sheets(1)
Set rngConnection = shtConnection.Range("A1")
strWbkPath = wbkConnection.Path
If Right(strWbkPath, 1) <> "\" Then
strWbkPath = strWbkPath & "\"
End If
strConnection = """TEXT;" & SQLSTR & """"
MsgBox strConnection
With shtConnection.QueryTables.Add( _
Connection:=strConnection, _
Destination:=rngConnection, _
Sql:=cstrCommandText)
.Name = "QueryTest"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileTabDelimiter = True
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With
Set rngConnection = Nothing
Set shtConnection = Nothing
Set wbkConnection = Nothing
End Sub
 
J

Jac Tremblay

Hi Joel,
Thanks for that quick reply. But I still get an error.
First, there is no such method as ReadAll for a FileSystemObject (in Excel
2000). So I used the good old way with Input and Freefile.
Here is the code modified.
' ***********************************************************
Sub ConnectionViaTextFile()
Dim wbkConnection As Workbook
Dim shtConnection As Worksheet
Dim rngConnection As Range
Dim strWbkPath As String
Dim strConnection As String
Const cstrTextFile As String = "ConnectionText.txt"
Const cstrCommandText As String = _
"SELECT TRET_PREV_ASRA.PROG_ASSU, TRET_PREV_ASRA.PROD_ASSU, " & _
"TRET_PREV_ASRA.AN_ASSU, TRET_PREV_ASRA.D_DEB_ASSU, " & _
"TRET_PREV_ASRA.D_FIN_ASSU, TRET_PREV_ASRA.REVE_STAB, " & _
"TRET_PREV_ASRA.PRIX_MARC, TRET_PREV_ASRA.TAUX_COTI_UNIT, " & _
"TRET_PREV_ASRA.TAUX_COTI_PLAN_CONJ, TRET_PREV_ASRA.UNIT_COTI, " & _
"TRET_PREV_ASRA.UNIT_COMP, TRET_PREV_ASRA.TIMB_MAJ, " & _
"TRET_PREV_ASRA.USAG_MAJ" & vbCrLf & _
"FROM " & _
"""OPS$DEVLIB01""" & _
".TRET_PREV_ASRA TRET_PREV_ASRA" & vbCrLf & _
"ORDER BY TRET_PREV_ASRA.PROG_ASSU, TRET_PREV_ASRA.PROD_ASSU, " & _
"TRET_PREV_ASRA.AN_ASSU"
Set wbkConnection = ActiveWorkbook
Set shtConnection = wbkConnection.Sheets(1)
Set rngConnection = shtConnection.Range("A1")
strWbkPath = wbkConnection.Path
If Right(strWbkPath, 1) <> "\" Then
strWbkPath = strWbkPath & "\"
End If
' *****
Dim intFreeFile As Integer
intFreeFile = FreeFile
Open strWbkPath & cstrTextFile For Input As #intFreeFile
Input #intFreeFile, strConnection
Close #intFreeFile
strConnection = """TEXT;" & strConnection & """"
MsgBox strConnection
' *****
With shtConnection.QueryTables.Add( _
Connection:=strConnection, _
Destination:=rngConnection, _
Sql:=cstrCommandText)
.Name = "QueryTest"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileTabDelimiter = True
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With
Set rngConnection = Nothing
Set shtConnection = Nothing
Set wbkConnection = Nothing
End Sub
' ***********************************************************
It bugs at the same place. I tried to add double quotes in the text file but
it doesn't work.
What else can you see?
Thanks again.
 
J

Jac Tremblay

Hi again, Joel,
For your information, the error I get now is "No 5: Argument or incorrect
procedure call".
Thanks.
 
J

Joel

My connnection statement looks like this for one of my databases

Connection:=Array(Array( _
"ODBC;" & _
"DSN=ABC Database;" & _
"DBQ=C:\TEMP\Part Log.mdb;" & _
"DefaultDir=C:\TEMP;" & _
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout"), _
Array("=5;")), _
Destination:=Range("A1"))


If I put this in a file it would look like this

Array(Array("ODBC;DSN=ABC Database;.......;PageTimeout"),Array("=5;")),
destination:=Range("A1"))

Note : It would be all on one line. Some times if the line is too long for
a statement you need to make multiple lines. Each line should end with a
comma.

I believe
 
J

Jac Tremblay

Hi Joel,
I found the error. I removed the following line:
' strConnection = "TEXT;" & strConnection
I let the connection string be exactly what is stored in the text file. And
it works.
The reason that I added this text: "TEXT;" is that I found this information
in the help file which goes like this:
Example to specify a connection via a text file:
Workshets(1).QueryTables(1) _
Connection := "TEXT;C:\My Documents\19980331.txt"
I guess this info is outdated.

Thanks for your help.
By the way, my connection is through Oracle 930 and the connection string
looks like this:
"ODBC;DSN=Oracle 930;UID=ops$devlib01;PWD=test;SERVER=unit;"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

excel instance stays open 3
Macro work - but don't think it is very efficient 0
Progress Bar Internet Connection 0
Open mail merge doc as read only 5
Refresh a local web page via Excel 3
Data Connection Proliferation 2
Excel instance remains open 7
Non-exclusive file access via QueryTable 0

Top