Hi All,
I admit I am a complete novice on this aspect of using
Excel. My requirement is as follows.

I have an Access Database called nt1.mdb on drive D: in
which there is one table (Table1) of data, indexed on
column1

I wish to connect to this database and search the contents
of Table1 for a value in column1 that has been entered
into an Excel userform. The values of the other columns
in Table1 should be returned if the value is found.

How do I connect to the mdb, do the search and read the
resulting values from the table?

Any help gratefully received.
Cheers

Try using the Macro recorderto being with:

Data, Import External data, New Database Query. Select MS Access as the
Source, then point to your .MDB
The rseulting code will look something like:

----------------------------------------------------------------------------
-----------------
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and Settings\rsw\My
Documents\db2.mdb;DefaultDir=C:\Documents and Settings\rsw\My Docum" _
), Array("ents;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT ctx415_unit.act_rateable_val, ctx415_unit.addr_num,
ctx415_unit.addr_road, ctx415_unit.address1, ctx415_unit.address2,
ctx415_unit.address3, ctx415_unit.address4, ctx415_unit.alarm, ctx415_unit"
_
, _
".amount1, ctx415_unit.amount10, ctx415_unit.amount2,
ctx415_unit.amount3, ctx415_unit.amount4, ctx415_unit.amount5,
ctx415_unit.amount6, ctx415_unit.amount7, ctx415_unit.amount8,
ctx415_unit.amount9, " _
, _
"ctx415_unit.area, ctx415_unit.ass_ten_rent, ctx415_unit.available,
ctx415_unit.beds, ctx415_unit.building, ctx415_unit.cancel_from_debit,
ctx415_unit.comment1, ctx415_unit.comment2, ctx415_unit.commen" _
, _
"t3, ctx415_unit.comment4, ctx415_unit.company, ctx415_unit.core10,
ctx415_unit.core7, ctx415_unit.core8, ctx415_unit.core9,
ctx415_unit.date_improved, ctx415_unit.date1, ctx415_unit.date10,
ctx415_uni" _
,,,,,,,)
.Name = "Query from MS Access Database"
.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

----------------------------------------------------------------------------
--------------------------------

You should be able to edit this to something nearer:
----------------------------------------------------------------------------
--------------------------------
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=MS Access Database;DB=
D:\nt1.mdb;DefaultDir=D:\;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;"), _
Destination:=Range("A1"))

.Sql = mySQL

.Name = "Query from MS Access Database"
.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

----------------------------------------------------------------------------
--------------------------------
In the above, a SQL string is assigned to mySQL, something like

SELECT * from table1 WHERE fieldname = " & myTextBoxValue &"


This example has been done in a hurry, hope it helps!


--
Roger
Shaftesbury (UK)
















"RSM" wrote in message
...
Hi All,
I admit I am a complete novice on this aspect of using
Excel. My requirement is as follows.

I have an Access Database called nt1.mdb on drive D: in
which there is one table (Table1) of data, indexed on
column1

I wish to connect to this database and search the contents
of Table1 for a value in column1 that has been entered
into an Excel userform. The values of the other columns
in Table1 should be returned if the value is found.

How do I connect to the mdb, do the search and read the
resulting values from the table?

Any help gratefully received.
Cheers