Connecting to access
 

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

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

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

I'm having problems!!!

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

Please help

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

Connecting to access
 

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

Connecting to access
 

To my namesake, the OP

I can't answer your question and only responding as we post with the same
name.

I note as of yet you have not replied to Jim's suggestions and I think I see
why - you are posting to this ng via www.officehelp.in

Whilst this site may offer some services, accessing MS's public newsgroups
does not appear to be one of them. Your post has only just appeared here
http://www.officehelp.in/index/forum/59-1.html
some sixteen hours after you posted. At time of writing Jim's message has
not yet been updated on this site.

I'm sure you would find it much easier to access this ng in a news reader,
eg Outlook Express.
microsoft.public.excel.programming

Alternatively you could sign up to Google and post to this ng via
Google-Groups. You can of course read this ng in Google-groups without
signing up.

The only advantage (IMO) to the "new" vs "old" Google-groups is that it
updates very quickly. But if you ever copy code, first select options and
view original message.

Regards,
Peter T


"Peter T" wrote in message
...

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

Connecting to access
 

Thanks for that info Peter, I didn't know what was going wrong and why
I wasn't getting replies. My office appears to be blocking newsgroups
so I will have to do this through google. Thanks for the tip about
copying from original message.

Thanks for the info Jim I'll sit here and digest it for a while. To
tell you the truth I have been having problems understanding all the
info I've read on the connecting from Excel to Access but am struggling
through it and will solve this.... I hope.

Thanks again to both of you