Consulting Access from Excel
 

Hi, I working with some macros in Excel and i need to consult a DB in
Access. Does anybody know how can i do that?? and, besides the
consulting, how can i actualize one record, or how to move that
information into TextBox that i have in Excel??
I'll be waiting your answer!!
bye

Consulting Access from Excel
 

Hi Sofia
You might check out the FREQUENCY and HISTOGRAM functions in Excel help.
They look to be similiar to the Partition VBA function that you are using,
judging by the description of Partition in VBA help. (I have never used
Partition)
Please note that the HISTOGRAM function is only available with the Aalysis
ToolPak loaded.
You obviously know how to use Access, but I do not know how familiar you are
with Excel, so if you need a general discussion of using statistical
functions in Excel, please see:
http://office.microsoft.com/training...RC010919231033
for information on how to use Excel statistical functions.
Please let us know if this helps or if you need clarification or further help.
SongBear

" wrote:

Hi, I working with some macros in Excel and i need to consult a DB in
Access. Does anybody know how can i do that?? and, besides the
consulting, how can i actualize one record, or how to move that
information into TextBox that i have in Excel??
I'll be waiting your answer!!
bye

Consulting Access from Excel
 

Hi Sofia,
Please accept my appologies, I crossed your reply window with another.
Your answer is not a short one, except the "Does anybody know how..." part.
That answer is "Yes."
There are two main possibilities. I will write up a short description of
each and post that when it is finished. It may take a while.
SongBear

"SongBear" wrote:

Hi Sofia
You might check out the FREQUENCY and HISTOGRAM functions in Excel help.
They look to be similiar to the Partition VBA function that you are using,
judging by the description of Partition in VBA help. (I have never used
Partition)
Please note that the HISTOGRAM function is only available with the Aalysis
ToolPak loaded.
You obviously know how to use Access, but I do not know how familiar you are
with Excel, so if you need a general discussion of using statistical
functions in Excel, please see:
http://office.microsoft.com/training...RC010919231033
for information on how to use Excel statistical functions.
Please let us know if this helps or if you need clarification or further help.
SongBear

" wrote:

Hi, I working with some macros in Excel and i need to consult a DB in
Access. Does anybody know how can i do that?? and, besides the
consulting, how can i actualize one record, or how to move that
information into TextBox that i have in Excel??
I'll be waiting your answer!!
bye

Consulting Access from Excel
 

Sofia,

First, I am going to assume that by "macros in Excel", you mean you are
working with Excel VBA code.

There are, generally, two main methods of retrieving data from an Access
database.

One is using a direct query from a worksheet. Once refreshed, this data
would then be available to Excel formulas and macros.

The other method is...generally... to create a Database object in your Excel
VBA code, open your Access db into that object and retrieve the data using
Access VBA code.

Setting Up the Data source:

For the direct query method, you would need to establish a data source that
points to your Access database. You will need to do this using the €œData
Sources (OBDC)€� tool. In Windows XP this is found in Control Panel |
Administrative Tools.

Click on this tool and you will get the €œODBC Data Source Administrator€�
with the User DSN tab showing; click the Add button on the right.

This will call a €˜create new data source dialog. You must first choose
€œMicrosoft Access Driver(*.mbd)€�, then click the €œFinish€� button below the
tab window.

You will then get an €˜ODBC Microsoft Access Setup dialog. In this dialog
you will supply a name for the data source that you will recognize and
description of the data source. This can be simply the name and description
of the database.

Below that you will be able to select the database from which you wish to
retrieve records. Click Select and navigate to the Access database and select
it, then click OK.
The full system address of the selected database will then appear in the
Database box of the €˜ODBC Microsoft Access Setup dialog. Click OK and the
new data source will appear in the User DSN tab window of the €œODBC Data
Source Administrator€�. This data source will be used in the next step. Close
the Data Source Administrator.

Create an Excel worksheet Query against an external database:

The next step is to create a worksheet query in Excel.
To see how it works, create a new empty worksheet in your workbook. In the
clean sheet, click on the top menu: €œData | Import External Data | New
Database Query...€�
This will call up a dialog box from an external program named Microsoft
Query. This is the Choose Data Source dialog box.

In the Databases tab, you will see the new data source that you created.
Select it and, below that, check the €œUse the Query Wizard to create/edit
queries€� box then click OK.

If all goes well, you will see a €œQuery Wizard €“ Choose Columns€� dialog. It
will show all of the Tables and Queries available in the database that you
connected this data source to in the previous steps.

There are two basic ways to go from here.

If your data needs are complex and spanning multiple tables, you can create
an Access query first and treat it as a single table.

If your data is from only one table and your filtering needs are simple,
double click the table name and choose the columns (field names) that you
want to retrieve data from as well as any fields you want to use to filter
the data, and then click next.

You will then see the next dialog in the wizard, the €œQuery Wizard €“ Filter
Data€� dialog.

Sofia, if you are familiar with Access, you probably already know how this
dialog works, you will have seen it while creating Reports in Access. Select
the column or columns on which you want to filter the data and select the
type of filtering and the information key to filter on.

When you are finished with this, click Next to go to the €œQuery Wizard €“
Sort Order€� dialog. This box is also familiar to Access users. Select the
columns to sort by and select Ascending or Descending for up to three
cascading sorts.

At the completion of the sort order dialog, click Next and you will be taken
to the Query Wizard €“ Finish dialog. Your choices here are to return the data
to Excel, look at the data in Microsoft Query, or create€¦well, we will not
worry about OLAP cubes tonight. You can also save the query to a separate
file.

Basically, just return the data to Excel so that you can see what you have
been creating.

The data can then be accessed from your VBA code and placed in text boxes or
from formulas in Excel.

Sofia, it is late tonight. I am an old guy and I need my less-ugly sleep.
Tomorrow evening I will cover the other way to get Access data into Excel,
the VBA route.


" wrote:

Hi, I working with some macros in Excel and i need to consult a DB in
Access. Does anybody know how can i do that?? and, besides the
consulting, how can i actualize one record, or how to move that
information into TextBox that i have in Excel??
I'll be waiting your answer!!
bye

Consulting Access from Excel
 

Sofia,
I will use some code from a personal spreadsheet of mine to demonstrate how
I am doing a thing that is similar to what you describe.

In Excel, you should go to the Visual Basic Editor and make sure that the
Access Object library and DAO (data object library) are checked in
Tools|References.

Then you need to dimension some Access objects that you will populate with
your actual existing database later.

Private Sub RecallLastSessionData()

Dim ws As Object 'access workspace
Dim db As Object 'access database
Dim tb As Object 'access table
Dim rst As Recordset

Dim stPath As String
Dim stTableName As String 'table name comes from worksheet name
Dim stQry As String

On Error GoTo NoDB

'Next statement: My Access db is in the same directory as My Excel workbook.
'Substitute the name of your actual Access database as indicated.

stPath = ActiveWorkbook.Path & "\YourDataBase.mdb"

'Next statement: In my app, the Table name in the DB is the same as the
worksheet name from which the macro is called.
€˜Here I am creating a string variable that contains the name so that I can
use it later to €˜retrieve data from the table.

stTableName = ActiveSheet.Name

'Next: If this procedure is copied to a new sheet, this will alert user to
keep the sheet name
'to a legal length for an access table name.

If Len(stTableName) 64 Then
stTableName = InputBox("You need to shorten this data table name. " &
stTableName & " A table name
cannot be over 64 Characters. This one is " & Len(stTableName) & "
characters long.", "Worksheet name is too long
for an Access table name.", stTableName)
End If

'Next: if the database exists, then I want ot create an Access workspace and
open the DB in it.
'Look up CreateWorkspace Method in VBA help files in Access or in Excel
after you have established a
'reference to the Microsoft Access 11.0 Object Library and one of the
Microsoft DAO Object Librarys,
'in my case the 3.6 Object Library.

If (Dir(stPath) < "") Then
Set AXS = New Access.Application
Set ws = AXS.DBEngine.CreateWorkspace("DOH", "ADMIN", "", dbUseJet)
AXS.Visible = False 'I really don't need to see the db.
Set db = ws.OpenDatabase(stPath)

'Next: You will need to know your table name and the field (or column)
'names where the data is. At this point you are still inside the if statement.

Set tb = db.tabledefs(stTableName)
Else 'this else is in case the database is not found
GoTo NoDB
End If

'Next: use this statement to get your macro running, then remember to remove
it for mission critical apps.
'(Always work on a copy of your spreadsheet for developing.

On Error GoTo 0

'Next: Build a SQL select statement and assign it to a string variable. This
one opens the whole table,
'all fields, and does not filter. If the table is large, you might want to
add filtering to the SQL statement.
'One trick is to use Access to create a suitable query and then come back
and re-create the SQL in this string variable.
'Note the use of your table name string variable to build this larger
string. Also I used a specific known field name for ordering.

stQry = "SELECT " & stTableName & ".* FROM " & stTableName & " ORDER BY
[Time_Stamp] ASC;"

'Next: Open a recordset using that SQL statement

Set rst = db.OpenRecordset(stQry, dbOpenDynaset)

'Next: since this code is in a specific worksheet's code window, it already
knows which worksheet these ranges belong to,
'you just have to tell it the range addresses. If this code were in a
general module, you would have to specify
'both the worksheet and the ranges.

Range("I16:I17").ClearContents
Range("I23:I24").ClearContents

'Next: MoveLast is an Access VBA command; you can use it here because of
your Access references.
'Note that I already know the field names that I want to use. As you can
see, it is simple to put data from Access
'into Excel cells. 'With' statements can be nested. In other words, you can
say 'With' the recordset, as below,
'and then inside of that recordset 'With', 'With' a certain worksheet . You
can even close the worksheet 'With' when
'you are finished with it and open another worksheet "With", or ShapeRange
'With" for a worksheet, all from inside the original recordset
'With' statement. Just remember to 'End With' each 'With' statement when you
are finished with each one.
'My use of the data (on my worksheet) is below, you would substitute your
own field names from your own database table:

With rst
.MoveLast
Range("I23").Value = .Fields("Total_Wins")
Range("I24").Value = .Fields("Total_Losses")
Range("I16").Value = .Fields("Tracking_Wins")
Range("I17").Value = .Fields("Tracking_Losses")
End With

'Note that I was putting data into cells of the worksheet. Sofia wants to
put information into a Textbox, 'presumably on the worksheet. The easiest way
is to put your curser inside the text box and then go to the
'formula bar for that worksheet and click "=" and then click on a worksheet
cell that has the data that
'you want to display. That worksheet cell would be what you update with the
Database data. This cell can be on a different worksheet. For instance, if I
wanted to display some data
'retrieved above in a text box on another worksheet (in the same workbook),
then I would click the border
'of that text box and then click in the formula bar of that worksheet. Click
the equal key on the keyboard
'and then navigate to the worksheet with the ranges that are being updated
from your recordset.
'Click the cell that contains the data you want to display in the textbox on
the other sheet and hit Enter.

rst.Close
db.Close
ws.Close

AXS.Quit

PktSht
Exit Sub
NoDB:
stopit = MsgBox("There is no database set up. You must use the Send Session
To Database button first to set up
a database and give it a first record before running the update last session
from database macro.",
vbOKOnly)
If stopit = 1 Then Exit Sub

End Sub


Sofia, you can also access textboxes directly from code, but it is not as
straightforward at first as you might think.
Each worksheet has an invisible drawing layer which you might picture as
floating over it. If you draw a rectangle on that layer it looks like the
rectangle is on the worksheet. This rectangle is part of the "Shapes"
collection, called the ShapeRange, for that worksheet, as are all of the
Shapes on that worksheet. VBA is designed to handle objects in collections,
like the collection of worksheets in the workbook, and the collection cells
in a range on a worksheet, etc. For a discussion of how VBA uses the
ShapeRange to access individual Shape Objects, see Shape Object and
ShapeRange in VBA Help.
The rectangle itself does not contain text. Instead some drawing objects in
the shape collection can contain a speical OLE object called a TextFrame. For
a discussion, look up TextFrame in VBA Help.

If I was to use text boxes instead of worksheet cells, my code might look
like this:

YadaYadaYada
With rst
.MoveLast
Shapes(2).TextFrame.Characters.Text = .Fields("Total_Wins")
Shapes(3).TextFrame.Characters.Text = .Fields("Total_Losses")
Shapes(4).TextFrame.Characters.Text = .Fields("Tracking_Wins")
Shapes(5).TextFrame.Characters.Text = .Fields("Tracking_Losses")
End With
YadaYadaYada
End Sub, etc.

Note that my Shapes(1) is the CommandButton that I use to run the macro.
This is important because it tells you that you need to make note of which
rectangle in the worksheet is which shape number in the range. You could
write a short macro that writes each shape's number inside of itself, running
through all of the shapes as a collection and testing for those that have
textframes. Please do this on a copy of your worksheet because you will
replace any text in boxes that was meant to be permanent as well as finding
the ones meant to hold data. The shapes are automatically numbered as they
are added to the sheet and might not be where you expect them to be. Also,
all shapes are part of the collection (like the CommandButton in mine in the
example) and your text boxes might not be numbered 1, 2, and 3.

Sofia, Please let us know if this answers your question, or if you need
further help or clarification.
SongBear

" wrote:

Hi, I working with some macros in Excel and i need to consult a DB in
Access. Does anybody know how can i do that?? and, besides the
consulting, how can i actualize one record, or how to move that
information into TextBox that i have in Excel??
I'll be waiting your answer!!
bye