Define Data source Access to Excel

[thefox149]

I have a spreadsheet that is linked to a access database
using the Get External Data etc

I have some users that also have the same database in another state capturing different data.

Although the data is different the report output is the same. I would like to send them the spreadsheet and link it to their database so they can utiles the functions I have written in Excel VBA and named range properties to auto update charts etc.

My theory would be a connection string would need to be placed in a on workbook open sub. Also a file Open dialogue box would be great so that they can navigate to the database

Any help would be greatly appreciated

 

[PHV]

Have a look at the QueryTables collection of the Worksheet object and at the Connection property of the QueryTable object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

I use this technique on a regular basis for querying Excel and Access.

Turn on the macro recorder.

Edit the Query & Return Data to Excel.

Turn off the recorder. Then clean up & organize the generated code.

You'll see the connect string. You can use the GetOpenFilename method to prompt the user to identify another Access DB. Parse the Path and Db Name (sans extension) to use by concatenation in BOTH the connect string AND the FROM CLAUSE of the SQL.


Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[thefox149]

Here is my code it almost works however I get the error message "Destination Range is not the Same worksheet that the Query table is being created on"

I store the path dir in a text box so that it know where to look as well as the user makes sure they have selected the right source...any ideas

Sub getdata()

Set exApp = CreateObject("Excel.Application")
fileToOpen = exApp.GetOpenFilename("All Access (*.mdb), *.mdb")
MsgBox fileToOpen
Me.txtPath = fileToOpen


Sheets("Data").Select

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=" & txtPath & ";DefaultDir=" & txtPath & "" _
), Array( _
";Driver={Driver do Microsoft Access (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;Ma" _
), Array( _
"xScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT tblRecovery_rpts.Date, tblRecovery_rpts.Centre, tblRecovery_rpts.`MTH PAYMENTS`, tblRecovery_rpts.`MTH RECOVERIES`, tblRecovery_rpts.`MTH %`, tblRecovery_rpts.`RTM PAYMENTS`, tblRecovery_rpts.`" _
, _
"RTM RECOVERIES`, tblRecovery_rpts.`RTM %`, tblRecovery_rpts.Product, tblRecovery_rpts.State" & Chr(13) & "" & Chr(10) & "FROM tblRecovery_rpts tblRecovery_rpts" _
)

.Name = "Query from Recoveries"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Sheets("Control Panel").Select
End sub

 

[SkipVought]

You may have more than one querytables on your sheet. In the Name Box, see how many names there are on that sheet like
[tt]
Query_From_Access...
[/tt]
BTW, the DBQ & DefaultDir are DIFFERENT values.

Sub getdata()
    Dim sConn As String, sSQL As String, sPath As String, sDB As String
    
    Set exApp = CreateObject("Excel.Application")
    fileToOpen = exApp.GetOpenFilename("All Access (*.mdb), *.mdb")
    MsgBox fileToOpen
    Me.txtPath = fileToOpen

    sPath = Left(fileToOpen, InStrRev(fileToOpen, "\") - 1)
    
    sDB = Split(fileToOpen, "\")(UBound(Split(fileToOpen, "\")))

    sConn = "ODBC;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "Driver={Driver do Microsoft Access (*.mdb)};"
    sConn = sConn & "DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"

    sSQL = "SELECT R.Date"
    sSQL = sSQL & ", R.Centre"
    sSQL = sSQL & ", R.`MTH PAYMENTS`"
    sSQL = sSQL & ", R.`MTH RECOVERIES`"
    sSQL = sSQL & ", R.`MTH %`"
    sSQL = sSQL & ", R.`RTM PAYMENTS`"
    sSQL = sSQL & ", R.`RTM RECOVERIES`"
    sSQL = sSQL & ", R.`RTM %`"
    sSQL = sSQL & ", R.Product"
    sSQL = sSQL & ", R.State "
    sSQL = sSQL & "FROM tblRecovery_rpts R "

    With Sheets("Data").QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[thefox149]

Thanks Skip

Nice looking code (better than marco recorder)

 

[thefox149]

Guys this doesn't work in 97

I think instring doesn't work in 97 any suggeestions

 

[SkipVought]

Split does not work in Excel 97. InStrRev does.

The InStrRev function returns the position of the first occurrence of the search string from the RIGHT.

That position can ALSO be used to pick off the RIGHT portion of the FileName/Path string

sDB = Right(filename, Len(filename) - InStrRev(filename, "\"))

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[thefox149]

sorry but I don't think InstrRev works either in 97
PHV wrote something that I am not sure how it applies
here is the link to the thread

http://www.tek-tips.com/viewthread.cfm?qid=828671

 

[thefox149]

solved it with this nice piece of work

Public Function InStrRight(vSearchStr As String, vTargetStr As String, vStart As Long) As Long
Dim I As Integer
For I = vStart To 1 Step -1
If InStr(I, vSearchStr, vTargetStr, 1) = I Then
InStrRight = I 'Position of vTargetStr
Exit For
End If
Next I
End Function

Seek and ye shall find