Re: ADO with ACCESS 2007 and EXCEL 2007?

Thanks for your interest!
I have done some more testing and believe that I have found some clues, but I don't yet know what to make of them:

With a general test program I once made to get experience with ADO I successfully connected to both versions of my excel workbook (and displayed Faktura$) using the ACE engine. (Yes, the testprogram showed the spread*** with this name!)

The breakthrough is that in this testprogram I have: ADODataSet1.CommandType = cmdTableDirect so I do not use any SELECT command statement here.

I discovered that in this workbook I have several named ranges one of which is also named Faktura and this showed up in the list of tables as just "Faktura". I have no idea yet if this fact is important.

Now I also discovered a peculiar discrepancy between the form specifications shown in the IDE and what I see in the dfm file:

The IDE shows ADODataSet1.CommandType = cmdtext but in the saved .dfm file ADODataSet1.CommandType is not shown at all? And as the default commandtype is cmdUnknown that really worries me.

Now let me scan your post below and give specific answers:

Yes, the spread*** "Faktura" exists

I connect after I have established the name of the workbook that I want to connect, either from OpenDialog or by reading a saved DSN from the registry (within an init process after the form has been created).

Thanks for your offer to look directly into my program, I am very grateful but believe i should try a bit more investigation before bothering you with that. Remember that the program does indeed work OK except for this one peculiar situation.

regards Sven

"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@xxxxxxxxx> wrote in message news:Xns9AD4D8FFB8714ChrisCheneytesconet@xxxxxxxxxxxxxxxx
"Sven Pran" <no.direct@xxxxxxxxxxx> wrote in
news:48727444@xxxxxxxxxxxxxxxxxxxxxx:

"Chris.Cheney"
I can't understand the problem with use of [ ] - could this be a
localisation issue? Is there a character set issue, e.g. umlauts?

I don't believe the problem is the use of [] becacuse I made another
test with FROM [Fakturering] and that worked exactly as all my tests
without the brackets around the spreadsheetname. I believe brackets
are optional unless required due to special characters being present
in the spread*** name?

Does the work*** exist or are you creating it by this means?

The problem here seems to be the trailing $ which apparently causes
the name to be "invalid"? However, I am not aware of any other
situation in Delphi, MS Office or Windows where a $ causes problems, I
can (and do) for instance use it as a legal character in filenames and
there is no problem specifying hexadecimal values using the $ prefix
in my Delphi source.

The trailing $ apparently indicates that the work*** already exists.

I have been playing around with an ADODataset (without the
ADOConnection) and have found a difference in use between JET and
ACE:

if I set ADODataset.Active := True; in the FormCreate, it's ok with
JET but
ACE complains that it is already in use. Moving the assignment to
FormShow and it's ok with both JET and ACE. I can't explain that!

In my program ADODataset1.Active is false until the first time a
connection is made. The ADODataset1.Close statement in my code is
there only to cater for the possibility that I make another
connection.

But what about your ADOConnection object? When does that connect?

What really puzzles me is that I have no problem with Jet and Excel
version 8 or ACE and Excel version 12, only with the combination ACE
and Excel version 8. (And the problem doesn't show until
ADODataset1.open when the SELECT statement is processed.)

Please observe that there is no difference in my code for the various
alternatives other than the differences in ADOConnection1.provider and
ADOConnection1.connectionstring?

I am worried.

The last time that I helped someone about ADO, it eventually turned out
that there was a space missing between "Data" and "Source" in the
connection string.

I'll be happy to take a closer look at the program and spread*** if you
would care to e-mail me direct (remove XXNOSPAMXX) with a zipped
attachment. However, I can't promise a result.

Chris

.