| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Access Database
How can I use an access data base from within Excel. I already have all the information that is in this database in a seperate worksheet and search this when I need to do so. I believe that the search criteria and speed is far better if I could utilise an access database but dont know how to do this from within excel. I would like to achieve this with a bit of VB behind a commandbutton. Have I asked too much in one question. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=510665 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Access Database
Have you investigated using Data - Get External Data - New Database Query?
You don't really specify in a lot of detail what you are trying to do. You can also retrieve an ADODB recordset (based on an Select query) which I have code for but if the first option will work then I recommend going with that... -- HTH... Jim Thomlinson "grahammal" wrote: How can I use an access data base from within Excel. I already have all the information that is in this database in a seperate worksheet and search this when I need to do so. I believe that the search criteria and speed is far better if I could utilise an access database but dont know how to do this from within excel. I would like to achieve this with a bit of VB behind a commandbutton. Have I asked too much in one question. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=510665 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Access Database
See this example
http://www.rondebruin.nl/accessexcel.htm I like the second example workbook where you can save the criteria -- Regards Ron de Bruin http://www.rondebruin.nl "grahammal" wrote in message ... How can I use an access data base from within Excel. I already have all the information that is in this database in a seperate worksheet and search this when I need to do so. I believe that the search criteria and speed is far better if I could utilise an access database but dont know how to do this from within excel. I would like to achieve this with a bit of VB behind a commandbutton. Have I asked too much in one question. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=510665 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Access Database
Very nice addition to your site Ron. I have not downloaded the workbooks yet
but I like the very generic and easy to follow way you create your SQL statement. I will in all likelyhood borrow some of your code for that. I use something similar but not as easy for the average non-SQL person to impliment. My only comment would be the second line on the page is "I go try to change that on this webpage." -- HTH... Jim Thomlinson "Ron de Bruin" wrote: See this example http://www.rondebruin.nl/accessexcel.htm I like the second example workbook where you can save the criteria -- Regards Ron de Bruin http://www.rondebruin.nl "grahammal" wrote in message ... How can I use an access data base from within Excel. I already have all the information that is in this database in a seperate worksheet and search this when I need to do so. I believe that the search criteria and speed is far better if I could utilise an access database but dont know how to do this from within excel. I would like to achieve this with a bit of VB behind a commandbutton. Have I asked too much in one question. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=510665 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Access Database
Thanks
I am also an beginner with Access so I hope it is useful for others Will add another page soon for sending info to Access My only comment would be the second line on the page is My English is bad<g, I go change that now Jim -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... Very nice addition to your site Ron. I have not downloaded the workbooks yet but I like the very generic and easy to follow way you create your SQL statement. I will in all likelyhood borrow some of your code for that. I use something similar but not as easy for the average non-SQL person to impliment. My only comment would be the second line on the page is "I go try to change that on this webpage." -- HTH... Jim Thomlinson "Ron de Bruin" wrote: See this example http://www.rondebruin.nl/accessexcel.htm I like the second example workbook where you can save the criteria -- Regards Ron de Bruin http://www.rondebruin.nl "grahammal" wrote in message ... How can I use an access data base from within Excel. I already have all the information that is in this database in a seperate worksheet and search this when I need to do so. I believe that the search criteria and speed is far better if I could utilise an access database but dont know how to do this from within excel. I would like to achieve this with a bit of VB behind a commandbutton. Have I asked too much in one question. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=510665 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Access Database
Hi Jim
Do you mean that I must add the "" -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Thanks I am also an beginner with Access so I hope it is useful for others Will add another page soon for sending info to Access My only comment would be the second line on the page is My English is bad<g, I go change that now Jim -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... Very nice addition to your site Ron. I have not downloaded the workbooks yet but I like the very generic and easy to follow way you create your SQL statement. I will in all likelyhood borrow some of your code for that. I use something similar but not as easy for the average non-SQL person to impliment. My only comment would be the second line on the page is "I go try to change that on this webpage." -- HTH... Jim Thomlinson "Ron de Bruin" wrote: See this example http://www.rondebruin.nl/accessexcel.htm I like the second example workbook where you can save the criteria -- Regards Ron de Bruin http://www.rondebruin.nl "grahammal" wrote in message ... How can I use an access data base from within Excel. I already have all the information that is in this database in a seperate worksheet and search this when I need to do so. I believe that the search criteria and speed is far better if I could utilise an access database but dont know how to do this from within excel. I would like to achieve this with a bit of VB behind a commandbutton. Have I asked too much in one question. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=510665 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Access Database
No worries... Me understand... My battle cry is "Bad spellers of the world...
Untie". Here is my code for an Access query. It accepts the Pieces of an SQL statement and returns either a connected or disconnected record set. The connected recordset is great as you can make you changes and just write them back with an update statement... Option Explicit 'Declare Module Level Constants Private Const m_cDBLocation As String = "C:\MyDatabse.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 On Error GoTo ErrorHandler 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 Sub Example() Dim rst As ADODB.Recordset Set rst = RunQuery("Select *", "From tblMyTable", "", ";", False) End Sub This requires a reference to the ADODB library... -- HTH... Jim Thomlinson "Ron de Bruin" wrote: Thanks I am also an beginner with Access so I hope it is useful for others Will add another page soon for sending info to Access My only comment would be the second line on the page is My English is bad<g, I go change that now Jim -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... Very nice addition to your site Ron. I have not downloaded the workbooks yet but I like the very generic and easy to follow way you create your SQL statement. I will in all likelyhood borrow some of your code for that. I use something similar but not as easy for the average non-SQL person to impliment. My only comment would be the second line on the page is "I go try to change that on this webpage." -- HTH... Jim Thomlinson "Ron de Bruin" wrote: See this example http://www.rondebruin.nl/accessexcel.htm I like the second example workbook where you can save the criteria -- Regards Ron de Bruin http://www.rondebruin.nl "grahammal" wrote in message ... How can I use an access data base from within Excel. I already have all the information that is in this database in a seperate worksheet and search this when I need to do so. I believe that the search criteria and speed is far better if I could utilise an access database but dont know how to do this from within excel. I would like to achieve this with a bit of VB behind a commandbutton. Have I asked too much in one question. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=510665 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Access Database
You don't have to add the "" but if you want to quote me, be my guest <g.
Just change "go" to "will" -- HTH... Jim Thomlinson "Ron de Bruin" wrote: Hi Jim Do you mean that I must add the "" -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Thanks I am also an beginner with Access so I hope it is useful for others Will add another page soon for sending info to Access My only comment would be the second line on the page is My English is bad<g, I go change that now Jim -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... Very nice addition to your site Ron. I have not downloaded the workbooks yet but I like the very generic and easy to follow way you create your SQL statement. I will in all likelyhood borrow some of your code for that. I use something similar but not as easy for the average non-SQL person to impliment. My only comment would be the second line on the page is "I go try to change that on this webpage." -- HTH... Jim Thomlinson "Ron de Bruin" wrote: See this example http://www.rondebruin.nl/accessexcel.htm I like the second example workbook where you can save the criteria -- Regards Ron de Bruin http://www.rondebruin.nl "grahammal" wrote in message ... How can I use an access data base from within Excel. I already have all the information that is in this database in a seperate worksheet and search this when I need to do so. I believe that the search criteria and speed is far better if I could utilise an access database but dont know how to do this from within excel. I would like to achieve this with a bit of VB behind a commandbutton. Have I asked too much in one question. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=510665 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Access Database
Just change "go" to "will"
Done Thanks -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... You don't have to add the "" but if you want to quote me, be my guest <g. Just change "go" to "will" -- HTH... Jim Thomlinson "Ron de Bruin" wrote: Hi Jim Do you mean that I must add the "" -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Thanks I am also an beginner with Access so I hope it is useful for others Will add another page soon for sending info to Access My only comment would be the second line on the page is My English is bad<g, I go change that now Jim -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... Very nice addition to your site Ron. I have not downloaded the workbooks yet but I like the very generic and easy to follow way you create your SQL statement. I will in all likelyhood borrow some of your code for that. I use something similar but not as easy for the average non-SQL person to impliment. My only comment would be the second line on the page is "I go try to change that on this webpage." -- HTH... Jim Thomlinson "Ron de Bruin" wrote: See this example http://www.rondebruin.nl/accessexcel.htm I like the second example workbook where you can save the criteria -- Regards Ron de Bruin http://www.rondebruin.nl "grahammal" wrote in message ... How can I use an access data base from within Excel. I already have all the information that is in this database in a seperate worksheet and search this when I need to do so. I believe that the search criteria and speed is far better if I could utilise an access database but dont know how to do this from within excel. I would like to achieve this with a bit of VB behind a commandbutton. Have I asked too much in one question. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=510665 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Access Database
It's a thing of beauty... I trust you don't mind if I lift your idea for my
own purpose... -- HTH... Jim Thomlinson "Ron de Bruin" wrote: Just change "go" to "will" Done Thanks -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... You don't have to add the "" but if you want to quote me, be my guest <g. Just change "go" to "will" -- HTH... Jim Thomlinson "Ron de Bruin" wrote: Hi Jim Do you mean that I must add the "" -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Thanks I am also an beginner with Access so I hope it is useful for others Will add another page soon for sending info to Access My only comment would be the second line on the page is My English is bad<g, I go change that now Jim -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... Very nice addition to your site Ron. I have not downloaded the workbooks yet but I like the very generic and easy to follow way you create your SQL statement. I will in all likelyhood borrow some of your code for that. I use something similar but not as easy for the average non-SQL person to impliment. My only comment would be the second line on the page is "I go try to change that on this webpage." -- HTH... Jim Thomlinson "Ron de Bruin" wrote: See this example http://www.rondebruin.nl/accessexcel.htm I like the second example workbook where you can save the criteria -- Regards Ron de Bruin http://www.rondebruin.nl "grahammal" wrote in message ... How can I use an access data base from within Excel. I already have all the information that is in this database in a seperate worksheet and search this when I need to do so. I believe that the search criteria and speed is far better if I could utilise an access database but dont know how to do this from within excel. I would like to achieve this with a bit of VB behind a commandbutton. Have I asked too much in one question. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=510665 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Access Database
That's why I run my site Jim Have fun with it -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... It's a thing of beauty... I trust you don't mind if I lift your idea for my own purpose... -- HTH... Jim Thomlinson "Ron de Bruin" wrote: Just change "go" to "will" Done Thanks -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... You don't have to add the "" but if you want to quote me, be my guest <g. Just change "go" to "will" -- HTH... Jim Thomlinson "Ron de Bruin" wrote: Hi Jim Do you mean that I must add the "" -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Thanks I am also an beginner with Access so I hope it is useful for others Will add another page soon for sending info to Access My only comment would be the second line on the page is My English is bad<g, I go change that now Jim -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... Very nice addition to your site Ron. I have not downloaded the workbooks yet but I like the very generic and easy to follow way you create your SQL statement. I will in all likelyhood borrow some of your code for that. I use something similar but not as easy for the average non-SQL person to impliment. My only comment would be the second line on the page is "I go try to change that on this webpage." -- HTH... Jim Thomlinson "Ron de Bruin" wrote: See this example http://www.rondebruin.nl/accessexcel.htm I like the second example workbook where you can save the criteria -- Regards Ron de Bruin http://www.rondebruin.nl "grahammal" wrote in message ... How can I use an access data base from within Excel. I already have all the information that is in this database in a seperate worksheet and search this when I need to do so. I believe that the search criteria and speed is far better if I could utilise an access database but dont know how to do this from within excel. I would like to achieve this with a bit of VB behind a commandbutton. Have I asked too much in one question. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=510665 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Access Database
What a relief. I have "Borrowed" lots of stuff. <bg
-- HTH... Jim Thomlinson "Ron de Bruin" wrote: That's why I run my site Jim Have fun with it -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... It's a thing of beauty... I trust you don't mind if I lift your idea for my own purpose... -- HTH... Jim Thomlinson "Ron de Bruin" wrote: Just change "go" to "will" Done Thanks -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... You don't have to add the "" but if you want to quote me, be my guest <g. Just change "go" to "will" -- HTH... Jim Thomlinson "Ron de Bruin" wrote: Hi Jim Do you mean that I must add the "" -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... Thanks I am also an beginner with Access so I hope it is useful for others Will add another page soon for sending info to Access My only comment would be the second line on the page is My English is bad<g, I go change that now Jim -- Regards Ron de Bruin http://www.rondebruin.nl "Jim Thomlinson" wrote in message ... Very nice addition to your site Ron. I have not downloaded the workbooks yet but I like the very generic and easy to follow way you create your SQL statement. I will in all likelyhood borrow some of your code for that. I use something similar but not as easy for the average non-SQL person to impliment. My only comment would be the second line on the page is "I go try to change that on this webpage." -- HTH... Jim Thomlinson "Ron de Bruin" wrote: See this example http://www.rondebruin.nl/accessexcel.htm I like the second example workbook where you can save the criteria -- Regards Ron de Bruin http://www.rondebruin.nl "grahammal" wrote in message ... How can I use an access data base from within Excel. I already have all the information that is in this database in a seperate worksheet and search this when I need to do so. I believe that the search criteria and speed is far better if I could utilise an access database but dont know how to do this from within excel. I would like to achieve this with a bit of VB behind a commandbutton. Have I asked too much in one question. -- grahammal ------------------------------------------------------------------------ grahammal's Profile: http://www.excelforum.com/member.php...o&userid=20336 View this thread: http://www.excelforum.com/showthread...hreadid=510665 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Update Excel Database via Access Main Database with a script | New Users to Excel | |||
| Access Database | Excel Discussion (Misc queries) | |||
| Access Database | Excel Programming | |||
| VBA Database access - How? | Excel Programming | |||
| VBA Database access | Excel Programming | |||
![grahammal[_15_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
