Since you have read up on ADO DB here is some code that I use to run a fairly
simple select query. It returns either a connected or disconnected record
set. Use the connected record set when you need to write back to the database.

Private Const m_cDBLocation As String = "Test.mdb"


Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _
ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean)
As ADODB.Recordset
Dim strConnection As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"

Set RunQuery = New ADODB.Recordset
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With

RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, strConnection, , , adCmdText
If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing
End Function

Use it like this...
Dim rst As ADODB.Recordset 'ADODB recordset from Access Database
Dim strSelect As String 'Select Statement Text
Dim strFrom As String 'Select Statement Text
Dim strWhere As String 'Select Statement Text
Dim strOrderBy As String 'Select Statement Text
'Create SQL statement
strSelect = "SELECT *"
strFrom = "FROM tblEmployess"
strWhere = "WHERE [EmployeeNumber] = 1"
strOrderBy = ";"

Set rst = RunQuery(strSelect, strFrom, strWhere, strOrderBy, True)

'To write back just use the line
rst.UpdateBatch
--
HTH...

Jim Thomlinson


"Peter T" wrote:


I have an Excel sheet called tool.xls that several (4 or 5) people at my
office are running copies of.

During the running of this spreadsheet it stores the person who is
running the sheets name and a long list (up to 1000) of account
numbers.

I want to have a button that they press which will export this list to
an access database and I want a drop down list (of the people tool.xls
names) that will enable them to import the list back into Excel for the
sheet to be able to use (thus allowing them to effectively save their
work overnight.)

I'm having problems!!!

I think I understand how to connect the two programs (I've read up on
threads like "Connection using ADO" number 136375) but I can't write a
line of code that selects 1000+fields to export or import.

Please help?


--
Peter T
------------------------------------------------------------------------
Peter T's Profile: http://www.officehelp.in/member.php?userid=60
View this thread: http://www.officehelp.in/showthread.php?t=657314
Visit - http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.php