Barbara Duprey wrote:
Thanks for your comments, Tim. Some comments/questions below....
Tim Deaton wrote:
Barbara Duprey wrote:
Tim Deaton wrote:
Barbara Duprey wrote:
I doubt that separating Base would have much in the way of
negative consequences -- it sort of follows the pattern that MS
chose in making Access a separate product or part of MS Office Pro
rather than the basic package. Since, as I understand it, no other
components depend on Base (unlike the interdependencies among the
others for common code), and it would decrease the size of the
primary download, this might be worth looking into. A couple of
other possible advantages would be the ability to update Base on a
different schedule with regard to the others, which could simplify
testing for both packages, and the opportunity to package the
major extensions as optional parts of the installation with more
visibility into their capabilities. I wouldn't expect the Base
user community to be upset by having a separate download for it.
I haven't yet used Base, tho I hope to one day replace Access97
with it. That's why I follow these discussions.
I'd have no problem with the idea of offering Base the way Access
is - both as a separate module and as an optional part of
OpenOffice. I DO want it to be able to interact well with Calc
(and with Excel) in terms of being able to read, import and export
table-style data to/from those spreadsheets. (I'm not familiar
enough to know how well it currently does those things.)
-- Tim Deaton
Currently, this kind of transfer works through the OS clipboard, and
there are a few things that could be smoother. You can get the job
done, though. I'm in the process of trying to come up with some kind
of UI definition for Import/Export functions, it'll appear in the
OOo wiki for comment/modification. Feel free to send me any ideas of
how you'd like it to work, either on or off this list!
I'm thinking about the way Access interacts with Excel. It can link
directly to a sheet within an Excel workbook, so long as the sheet is
organized as a table. In the linking process, you tell Access which
spreadsheet to open. It then displays the list of worksheets and you
pick the right one. Then you tell it whether the first row contains
the fieldnames or not. It scans the data and makes its own decisions
as to what kind of data each field is (numeric, text, etc). Once
that's done, the sheet is treated like an unindexed table. You can
edit, add, and delete records, and you can use the table in a query
or report. The lack of an index creates limitations as to how the
data can be used, but it makes creating useful reports of the Excel
data much easier.
Does all the editing happen in a localized copy of the table, or in
the sheet? The way Base handles the "import" function, there is no
specific connection with the data source;. The data from the clipboard
can be used to create a new table or append data to an existing one,
so the data is not necessarily a whole sheet. (I'm not really a
spreadsheet user, so I'm not sure whether a table in that context can
be a subset of a sheet.) If it's appending, it's even happy with the
database table having extra fields beyond those in the sheet (or word
processor table, or whatever). And you can do whatever you'd like with
the table without worrying about any conflict with the sheet. For
example, you can define an auto-increment field as an index (a primary
key for the table), and Base will create the appropriate values. I
like that flexibility, and it seems simpler than the multiple "update"
queries you apparently use in the sequences described below. I think,
though, that masking the data source through use ot the clipboard is
forcing the wizard to make some assumptions, and there are definitely
differences in what happens during the import coming form Calc, for
instance, vs. from Writer. What I'm thinking about has a similar kind
of action to select the data source more as you describe here, but I'd
add a capability to specify arbitrary row/column parameters once the
sheet, table, or whatever had been identified.
In Access, "Link" and "Import" are two separate things. And Access will
refuse to do either unless what's in the spreadsheet tab LOOKS LIKE a
table. (Within Excel, any section of rows & columns can be used like a
table; but in my testing, Access would not play with it unless
everything it saw looked like a table.) In either case, the clipboard
is not involved at all, so far as I can tell.
(I should also emphasize that I use Access 97. It uses DAO to do
things, where later versions of Access use ADO. That may cause some
differences in how they work.)
If I IMPORT the spreadsheet tab, then Access copies the data into an
Access table. It lets you choose whether to append it to an existing
table or to create a new one. If you choose to create a new one, you go
thru a process of choosing which columns to import, verifying or
changing the field names, and defining an index. (Appending is probably
similar, but I haven't tested that.)
If I LINK to the spreadsheet tab, then the source data IS the
spreadsheet. There's no clipboard, copy, or paste involved. If I make
changes to the data in that table, then the changes are made IN the
spreadsheet. (Generally, I don't think you'd want to make changes to
the spreadsheet; you'd only link to one to get to the data that's
already there.)
It also makes it very easy to import the data into a real database
table - which is what I mainly did. I got data daily in an Excel
format and cleaned it up if need be so that it fit into the
predefined Excel table. I then give that sheet the name that the
Access link would be looking for (while the Access database is
closed). Next, I opened my Access database and ran a set of
queries. The first query in the sequence used various functions to
format the data into the data-types that Access understood. That and
subsequent queries in the sequence might do other massaging to make
the data more useful. Then the last query in the sequence would
append the data to an existing Access table that also had a field
with an 'autonumber' data type as part of its index - thus insuring
that the index would see each new record as unique.
Now, about those update queries -- as far as I know Base currently
doesn't have GUI support for anything like that (though I'm not really
clear on the capabilities of form/subform use -- need to learn about
that sometime!). Instead, it provides a capability to execute SQL
commands, and you'd need those for the kinds of field conversions
you're describing. The import wizard lets you define the datatypes of
the fields, and it does its best to convert the data as it does the
"paste" -- I think there are some problems, though, especially with
dates. Mostly that's probably because OOo works with all sorts of
different operating systems and localizations, but the clipboard use
may also have an effect there.
Since Access (in linking) is treating the spreadsheet as just another
(unindexed) table, it can become a source table in a query. Because
it's unindexed tho, it can't do much of anything to 'relate' to other
data. And the Access wizards basically made their own datatype
decisions. If everything in the column looked like a date, then you get
a date field. If it all looks like times, you get a time field. If it
all looks like numbers, you get a 'double' field (double-precision
floating-point numbers). Anything else or any ambiguous column gets
defined as a 'text' field - probably of 50-characters length. To avoid
any unnecessary confusion, the first thing I do is use the functions
(the SQL commands you mentioned) in that first query to verify or change
them to the data-type that I need them to be. Then I feed the results
of that query into an 'append' query that adds the data to a real Access
table. (You mentioned 'update queries'. In Access, that's a different
animal that allows you to change the data in existing records, but not
to add any records.)
So far, we've been talking about importing data into the database. How
about exporting? For instance, can you specify that the results set of
a query should be stored in a worksheet? Is that kind of function of
any interest to you?
I am able to export query results to a spreadsheet in Access, but that
requires either a manual copy-and-paste or some Visual Basic code.
There are certain places where that comes in quite handy, tho.
(I hope this helps. I'm sorry to take so long replying, but it's
usually late at night before I get a chance to focus on it - and
sometimes that's too late to focus on anything.)
-- Tim
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
