trigger database query on cell data input

Hello

I have a question about programming Excel. I am new with excel
programming but I'm have experience with programming in other
languages.

I want to have an excel sheet populated with date from a database
query. I would like this query to be triggered by the user inputing
data in a cell and either hitting Enter or by clicking a Submit button.
For example if the user inputs in a designated cell a part number and
hits enter (or clicks a submit button) this will trigger a select * by
part bumber db query.

My question;

1. Is possible to do this in excell with VBA?
2. Are there any pointers (examples online) that I can inspire from?

Thanks
-D

D

It is certainly available with VBA but does it have to be?

Check here

http://www.nickhodge.co.uk/gui/datam...taexamples.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
[email protected]HIS


<[email protected]> wrote in message
news:[email protected]...
> Hello
>
> I have a question about programming Excel. I am new with excel
> programming but I'm have experience with programming in other
> languages.
>
> I want to have an excel sheet populated with date from a database
> query. I would like this query to be triggered by the user inputing
> data in a cell and either hitting Enter or by clicking a Submit button.
> For example if the user inputs in a designated cell a part number and
> hits enter (or clicks a submit button) this will trigger a select * by
> part bumber db query.
>
> My question;
>
> 1. Is possible to do this in excell with VBA?
> 2. Are there any pointers (examples online) that I can inspire from?
>
> Thanks
> -D
>

From a user Textbox you can initiate the Query using the AfterUpdate event
(if a userform) and then pass the value entered to a procedure
(myReadAccess), this procedure builds the SQL string and runs the database
query, if you do not use a userform then I would use a control button to
initiate the query taking a value entered into a cell. It is often easier
to use the macro recorder to build a template data connection and SQL using
Data->Import External Data->New Database Query from the Excel Toolbar, which
you can edit afterwards.

' in user form search string in myTextBox
Private Sub myTextBox_AfterUpdate()
Call myReadAccess(myTextBox.Value)
End Sub

' from a worksheet button - e.g. search string in A1
Private Sub CommandButton1_Click()
Call myReadAccess(Range("A1").Value)
End Sub


Sub myReadAccess(myData)
' set path and file to access database and read parameters
mdbPath = Worksheets("Data").Range("C2")
mdbFile = Worksheets("Data").Range("C3")
mdbBuffer = Worksheets("Data").Range("C7")
mdbTimeOut = Worksheets("Data").Range("C8")

'clear results range before loading new data - if required?
Range("A10:J10000").ClearContents

' build access database connection string
cConnection = "ODBC;" & _
"DBQ=" & mdbPath & mdbFile & ";" & _
"DefaultDir=" & mdbPath & ";" & _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"DriverId=281;FIL=MS Access;" & _
"MaxBufferSize=" & mdbBuffer & ";" & _
"PageTimeout=" & mbdTimeOut & ";"

' compose sql
cSQL = "SELECT column1, column2, column3" & Chr(13) & "" & Chr(10) & _
"FROM `" & mdbPath & mdbFile & "`.table" & _
Chr(13) & "" & Chr(10) & "WHERE (column1='" & myData & "')" & _
Chr(13) & "" & Chr(10) & "ORDER BY table.column1, table.column2"

' go get data
With ActiveSheet.QueryTables.Add(Connection:=cConnection,
Destination:=Range("A10"), Sql:=cSQL)
.FieldNames = True
.RowNumbers = True
.FillAdjacentFormulas = False
'.PreserveFormatting = True
.HasAutoFormat = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
'.AdjustColumnWidth = False
'.RefreshPeriod = 0
'.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub


--
Cheers
Nigel



<[email protected]> wrote in message
news:[email protected]...
> Hello
>
> I have a question about programming Excel. I am new with excel
> programming but I'm have experience with programming in other
> languages.
>
> I want to have an excel sheet populated with date from a database
> query. I would like this query to be triggered by the user inputing
> data in a cell and either hitting Enter or by clicking a Submit button.
> For example if the user inputs in a designated cell a part number and
> hits enter (or clicks a submit button) this will trigger a select * by
> part bumber db query.
>
> My question;
>
> 1. Is possible to do this in excell with VBA?
> 2. Are there any pointers (examples online) that I can inspire from?
>
> Thanks
> -D
>

Guys,

Thank you very much for your suggestions. They are greatly appreciated.


--Dan