PDA

View Full Version : ODBC Connect Help



pitchie
05-05-2011, 03:16 AM
Hi,

I have this code that works perfectly for searching for Data out of Sage Line 50:


If Cells(4, 1) <> "" Then 'If cells have data in them, delete it and the query
Range("A4:P5000").Select
Selection.ClearContents
Selection.QueryTable.Delete
End If
' Get all the data
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;Driver={Sage Line 50 v16};UID=manager;PWD=county;DIR=L:\Sage\ACCOUNTS\2010\COMPANY.002\ACCDATA;", Destination:= _
Range("A4"))
.CommandText = Array( _
"SELECT INVOICE.INVOICE_NUMBER, INVOICE.INVOICE_DATE, INVOICE.INVOICE_TYPE, INVOICE.ACCOUNT_REF, INVOICE.NAME, INVOICE.CUST_ORDER_NUMBER, INVOICE_ITEM.QUANTITY, INVOICE_ITE" _
, _
"M.DESCRIPTION, INVOICE_ITEM.UNIT_PRICE, INVOICE_ITEM.NET_AMOUNT, INVOICE_ITEM.TAX_AMOUNT, INVOICE_ITEM.GROSS_AMOUNT, INVOICE.CARR_NET" & Chr(13) & "" & Chr(10) & "FROM INVOICE INVOICE, INVOICE_ITEM INVOICE_ITEM" & Chr(13) & "" & Chr(10) & "WHERE INVOICE.IN" _
, _
"VOICE_NUMBER = INVOICE_ITEM.INVOICE_NUMBER AND INVOICE_ITEM.DESCRIPTION LIKE %" & Cells(2, 2) & "% ORDER BY INVOICE.INVOICE_NUMBER DESC" _
)
.Name = "Query from Ashfield Clutch Saga Data"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Range("B2").Select


However, as you can see, I am storing the password in the connection string as it is our small business and the connection is local. There are no security risks. However, despite the fact I am specifying the username password and location of the data in the connection string, it still brings up the input box to confirm all of this information.

Is there anyway that I can get this code to skip that box and just fetch the data as required without the needs for me having to OK that input box?

Thanks for your help in advance,

Paul.