Tim Deaton wrote:
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
Thanks, Tim. Yes, it helps. Base can put *some* functions into queries
in the GUI, but you usually have to drop into the SQL direct mode to do
much along those lines. I know the use of the clipboard is non-standard
-- that's why I'm working on sketching out a more traditional interface
(for Import and Export).
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
