(e-mail address removed) (cb) wrote ...
Thank you for your interesting points.
As it appears, I will have to use automation
to make sure the first eight rows in this one column
are either text or have an apostrophe, then use
transferspreadsheet to import the data into Access.
Not unless *all* the values in the column are non-text. If you set the
TypeGuessRows reg key to 0 (zero) all rows will be scanned to
determine whether a 'mixed types' situation exists. It takes just one
cell to hold a value with a different data type for 'mixed types' to
be triggered. When it does, if the ImportMixedTypes reg key is Text,
then the data type for the column will be Text. And remember than Text
means 255 characters max (that's a Jet limit).
I guess I question why you insist on dwelling on the distinction.
I figure it helps if people MS Access users can tell where MS Access
ends and MS Jet begins.
I quote from the Access 2000 VBA help:
"You can use the TransferSpreadsheet action to import or export data
between the current Microsoft Access database (.mdb) or Access project
(.adp) and a spreadsheet file."
So I guess I'm not the only one who thinks Access is a database).
My point. Even though I'm not usually a MS cynic, I see MS enjoy
blurring the distinction. If the general perception is, 'I can't
create, maintain and use a .mdb database because I don't have MS
Access', then who benefits? AFASIK I wouldn't even need a MS Access
licence to distribute a .mdb file to a client, let alone create or use
one for data storage.
I understand jet.dll and MSACCESS.EXE are two separate but integrated
applications
I wouldn't say they were 'integrated' because I can use a .mdb as a
database without having MS Access. I find it amusing I can use ADO to
create a column of type 'Hyperlink' even though it has no meaning
outside the MS Access UI.
yes, I use Access and Jet interchangeably, but they
are both provided by the same company (although questionable whether
it is an integrated team of developers that communicates).
I understand from reading these newsgroups that MS Access can be used
where MSDE or SQL Server is the main database (in these cases Jet
tables in the .mdb are still used to store settings required by the UI
e.g. for forms, reports, etc). SQL Server (and MSDE) is provided by
the same company as MS Access and Jet, so when MS Access is used as a
front end (there may be other front ends) to a SQL Server database, it
is legitimate to call the SQL Server database an 'MS Access database'?
I'd say, in context, yes it is reasonable to say that.,therefore, 'MS
Access database' and 'Jet'.
Jet is
merely the database engine used to serve data stored in Access .mdb
format to the other Access UI elements.
No, Jet additionally has elements that may be created and manipulated
from
You can't really do anything
useful in Access without using jet.
That doesn't make them the same thing. I see a clear distinction
between the 'Jet' elements of the .mdb and the 'MS Access only'
elements.
The TransferSpreadsheet method is
a VBA method. It does not really provide me an option whether to use
jet.
Is 'Spreadsheet' an MS euphemism for 'Excel' or does it support other
spreadsheet formats? If it means Excel only and it was called
TransferExcel then you'd have no option but to use Jet because Excel
uses Jet.
Your point is well taken that it is jet that is doing the import. So
I would argue that another way should be offered to developers using
the transferspreadsheet method to specify the data type. If you import
a text file or other delimited file via the wizard into Access you are
afforded this opportunity and I can almost guarantee that jet is
involved in this process.
Have you considered what transferspreadsheet does under the covers?
Even for someone like me who doesn't use MS Access VBA, its clear that
it is executing SQL under the covers e.g. for an import it does either
SELECT..INTO new table is required or a INSERT INTO..SELECT where an
existing table is involved [I don't mean sql text is compiled, and
executed; I'm sure something lower level happens, it's just sql is the
human readable equivalent.] Assuming a human can write better sql than
a machine, how would you or I write sql to determine the data type? We
could cast e.g.
SELECT
CSTR(MyExcelCol) AS MyJetCol
FROM
[Excel 8.0;HDR=Yes;Databse=C:\MyWorkbook.xls].[Sheet1$]
;
but we are too late: the data type has been determined before it gets
to the Jet on the MS Access side, so if there were text values in a
column determined to be numeric, we'd merely be converting nulls
rather than recovering the lost values.
I can guarantee you that Jet is involved in this process. What you may
not be seeing is that Jet is involved *twice* in the process e.g. this
is how I see it:
Excel native BIFF format
|
Jet Engine (Excel)
|
sql view of Excel Data
|
Jet Engine (MS Access)
|
Database native format
The determining of the Excel data type was done at the Jet Engine
(Excel) stage, over which Jet Engine (MS Access) has no control.
Consider this *SQL Server* sql syntax:
SELECT MyExcelCol AS MyMsSqlCol
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\MyWorkbook.xls;Extended Properties=Excel 8.0'
)...[Sheet1$]
This fetches the same data set (i.e. possibly unwanted nulls) as the
jet syntax above will (without the CSTR cast). I guess you could see
it as:
Excel native BIFF format
|
Jet Engine (Excel)
|
sql view of Excel Data
|
SQL Server
This is what I meant when I said, 'If you [sic] database is also Jet
then that is mere coincidence'.
You mention text files, which is a great example. When I use this Jet
syntax (say, from Excel VBA)
SELECT MyTextDateCol AS MyJetDateCol
FROM [Text; Database=C:\;].MyFile#txt
;
I get these results:
01/APR/2004
-------------
i.e. just a column header and no data. You say you have control over
the data types in the MS Access UI. Well, even though I'm using Excel,
I have similar control: in the same folder I create a file called
schema.ini with the following:
[MyFile.txt]
ColNameHeader=False
Format=CSVDelimited
Col1=MyTextDateCol Date
Now, I get these results:
MyTextDateCol
-------------
01/04/2004
i.e. a proper column header and the data as a date (I'm showing it in
my local date format).
Therefore, if we are requesting extensions to the Jet functionality
for the (highly unlikely) Jet version 5.0, I think we should be asking
that the schema.ini may be used when Excel is the data source.
If Microsoft is going to make Excel a half-baked database, they might
as well go a little further and at least make the data types rigorous.
I like Excel because it doesn't have strict data types by default,
meaning I can use it as a data 'sketchpad'. If I later need to make
the data typing a bit stricter I can use VBA if I'm a developer or
Validation if I'm a user: in the Excel UI choose Data, Validation,
dropdown the 'Any value' dropdown and you'll see some basic data
types. The item 'List' works a bit like a database's CHECK constraint
e.g.
CREATE TABLE Test(
gender CHAR(1) NOT NULL,
CHECK (gender in ('M','F'))
);
I try to use Access/(Jet) and Excel for what they excel at (pardon the
pun). I use Excel when I need complex charting or have to do other
complex calculations that Access/(Jet) does not handle well, or when
the customer absolutely insists on having the data in a flat file.
Without the Jet engine said:
I use Access/(Jet) because I can enforce data integrity and
reliability, optimize queries through indexing, and have some
security. Can you place indexes on data in Excel? How about enforcing
unique indexes?
A challenge, eh? How's your Excel? Open a new blank workbook. Sheet1,
column A will be our data column, with not column header. type enter
the values 1 to 4 in the cells A1 to A4. It will be easier if we
define a Name to refer to the data in this column: choose Insert,
Name, Define, in the 'Names in workbook' box type MyExcelCol, click
into the 'Refers to' box, hit F2 to enter edit mode, type the
following formula:
=OFFSET(Sheet1!$A:$A,0,0,COUNTA(Sheet1!$A:$A))
This is a standard Excel trick to create a Range that expands to fit
continuous data; let's not complicate the issue with non-continuous or
null data.
Now the validation. Select all cells (not just those with values) e.g.
by pressing the gray A column heading, choose Data, validation,
Settings tab, change the dropdown to show 'Custom', uncheck 'Ignore
blanks', click into the 'Formula' box, press F2 and enter the
following formula:
=AND(SUM(1/COUNTIF(MyExcelCol,MyExcelCol))=COUNTA(MyExcelCol),COUNTIF(MyExcelCol,"")=0)
OK to accept the validation rule.
Now, in cell A5 enter the value 5. That was OK because it is unique.
In cell A6 enter a previously used value, say 1. This time you should
see a message telling you the value has been disallowed.
Now, you are probably thinking lots of 'buts' e.g. 'But what if I use
VBA or sql to add a value?', the answers to which is usual, 'Yes, but
you can roll your own countermeasures using VBA.' Would I personally
choose to do such stuff in Excel rather than use a .mdb? Not a chance.
If you do all your "databases" in Excel, it must get quite
cumbersome..without using Access.
I'm more of a coder than a GUI person, so I write sql DDL scripts to
create all the elements I require. If I want to change something, I
can tweak the script, just delete the .mdb and run it again. And,
because I use standard sql syntax whenever possible, I can point the
script at, say, my SQL Server database and create a database with the
same schema there. In fact, there are something elements that are
difficult to do in the MS Access UI e.g. my aforementioned CHECK
constraint.
Not sure what you mean here--"using the Accesss UI", when you
don't own it.
My employer does so I can install it on a virtual machine as required
Also not sure what you mean by re-querying the Excel data.
Something like this (execute sequentially in the sql window of a query
object in MS Access UI):
CREATE TABLE
[Excel 8.0;Database=C:\cb.xls;].NewTable
(MyDateTimeCol DATETIME NULL)
;
INSERT INTO
[Excel 8.0;Database=C:\cbender.xls;].NewTable
(MyDateTimeCol) VALUES ('2004-10-14')
;
SELECT MyDateTimeCol
FROM
[Excel 8.0;Database=C:\cbender.xls;].NewTable
;
So one may use jet to create workbook and look at their 'sql view'
data and schema but do not much else without the Excel UI.
I have found the user forms library
used by Excel, Word, powerpoint is not quite the same as the forms
engine in Access. I don't remember the exact details.
We are entirely in agreement on this. Here are the exact details of my
first experience of MS Access form's, based on my knowledge of other
forms engines:
http://groups.google.com/[email protected]
I was using it in ESRI ArcMap
Ah ha, a geographer! Simply calling a .mdb a 'MS Access database' is
like setting the scale of a map too small: it results in too much
generalization and a loss of detail.
Jamie Collins B.Sc. (Hons) Geography
--
