docmd.transferspreadsheet unexpected error

[user_5701]

Hello, I'm getting an error with a Docmd.Transfers preadsheet line of
code:

DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel2000,
"tblTest", pathAndFilename , True

The above line works perfectly fine, but when I change the table name
argument (tblTest) to the name of a query, it fails and gives the
following error:

Runtime error 3275
Unexpected error from external database driver (22).

The help file with Access 2000 states that it is possible to use a
query instead of a table name, but a sql statement cannot be used - it
must first be saved in a query. I'm not using an sql statement, but
rather a saved query, and I still get the error. According to the
help file this should be working:

DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel2000,
"qryTest", pathAndFilename , True

Anybody have any ideas? Thanks in advance.

[Salad]

Re: docmd.transfers preadsheet unexpected error

user_5701 wrote:[color=blue]
> Hello, I'm getting an error with a Docmd.Transfers preadsheet line of
> code:
>
> DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel2000,
> "tblTest", pathAndFilename , True
>
> The above line works perfectly fine, but when I change the table name
> argument (tblTest) to the name of a query, it fails and gives the
> following error:
>
> Runtime error 3275
> Unexpected error from external database driver (22).
>
> The help file with Access 2000 states that it is possible to use a
> query instead of a table name, but a sql statement cannot be used - it
> must first be saved in a query. I'm not using an sql statement, but
> rather a saved query, and I still get the error. According to the
> help file this should be working:
>
> DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel2000,
> "qryTest", pathAndFilename , True
>
> Anybody have any ideas? Thanks in advance.[/color]

Do you have a small lookup table to make a test on? One that has 3 or 4
records? Create a query on that table. A real simple one. Ex:
Select ID, Description From LookupTable;
Does it work on your machine?

If so, then there is something in your query that is causing a problem.
Does you query have subqueries in it? Does it have have a function
call...ex NZ(), CINT(), or custom functions you have written? Is it a
Totals query where you are grouping the results? Do you have
calculated fields in it? IOW, is the query a simple select query or is
it a complex query?

It should work using your syntax. Finding out if it works with a real
simple query would be the first step to check. If not, start breaking
your query down (make a copy of the originial) and start removing
columns until it finally works.

[user_5701]

Re: docmd.transfers preadsheet unexpected error

Yes, all my queries are simple queries. I made a very simple query
which selected one field from a table, no where conditions or
anything, and it didn't work. I first thought it was the * that was
the problem, but that wasn't it. I went down the line and tried all
my reports and realized some it worked with, and some it didn't, but I
think I have the problem pinpointed now:

The name you give the excel spreadsheet file you're exporting to must
be 31 characters or less. If you name it 32 or more characters,
you'll get the error, but 31 or less will work fine. I was just
naming the spreadsheet the same name as the query and found some of my
queries with shorter names worked but a few didn't.

This is rather odd, and I wonder why Microsoft doesn't mention
anything about it in their help file for the transferspreads heet
command.

Does anyone know why this is?

Salad <oil@vinegar.co m> wrote in message news:<FM_fd.451 4$kM.2306@newsr ead3.news.pas.e arthlink.net>.. .[color=blue]
> user_5701 wrote:[color=green]
> > Hello, I'm getting an error with a Docmd.Transfers preadsheet line of
> > code:
> >
> > DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel2000,
> > "tblTest", pathAndFilename , True
> >
> > The above line works perfectly fine, but when I change the table name
> > argument (tblTest) to the name of a query, it fails and gives the
> > following error:
> >
> > Runtime error 3275
> > Unexpected error from external database driver (22).
> >
> > The help file with Access 2000 states that it is possible to use a
> > query instead of a table name, but a sql statement cannot be used - it
> > must first be saved in a query. I'm not using an sql statement, but
> > rather a saved query, and I still get the error. According to the
> > help file this should be working:
> >
> > DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel2000,
> > "qryTest", pathAndFilename , True
> >
> > Anybody have any ideas? Thanks in advance.[/color]
>
> Do you have a small lookup table to make a test on? One that has 3 or 4
> records? Create a query on that table. A real simple one. Ex:
> Select ID, Description From LookupTable;
> Does it work on your machine?
>
> If so, then there is something in your query that is causing a problem.
> Does you query have subqueries in it? Does it have have a function
> call...ex NZ(), CINT(), or custom functions you have written? Is it a
> Totals query where you are grouping the results? Do you have
> calculated fields in it? IOW, is the query a simple select query or is
> it a complex query?
>
> It should work using your syntax. Finding out if it works with a real
> simple query would be the first step to check. If not, start breaking
> your query down (make a copy of the originial) and start removing
> columns until it finally works.[/color]

[DMeister]

Re: docmd.transfers preadsheet unexpected error

The worksheet name (tabs at bottom that default to "Sheet1, Sheet2, etc.) in
a workbook is limited to 31 characters. Unless you supply a "range" argument
with the TransferSpreads heet Method, the query or table name will be used as
the sheet name and must conform to the 31 character limit or error 3275 is
thrown. May be documented somewhere. . . I learned by trial and error also.


"user_5701" <user_5701@hotm ail.com> wrote in message
news:f153016e.0 410280639.5bee7 d54@posting.goo gle.com...[color=blue]
> Yes, all my queries are simple queries. I made a very simple query
> which selected one field from a table, no where conditions or
> anything, and it didn't work. I first thought it was the * that was
> the problem, but that wasn't it. I went down the line and tried all
> my reports and realized some it worked with, and some it didn't, but I
> think I have the problem pinpointed now:
>
> The name you give the excel spreadsheet file you're exporting to must
> be 31 characters or less. If you name it 32 or more characters,
> you'll get the error, but 31 or less will work fine. I was just
> naming the spreadsheet the same name as the query and found some of my
> queries with shorter names worked but a few didn't.
>
> This is rather odd, and I wonder why Microsoft doesn't mention
> anything about it in their help file for the transferspreads heet
> command.
>
> Does anyone know why this is?
>
> Salad <oil@vinegar.co m> wrote in message[/color]
news:<FM_fd.451 4$kM.2306@newsr ead3.news.pas.e arthlink.net>.. .[color=blue][color=green]
> > user_5701 wrote:[color=darkred]
> > > Hello, I'm getting an error with a Docmd.Transfers preadsheet line of
> > > code:
> > >
> > > DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel2000,
> > > "tblTest", pathAndFilename , True
> > >
> > > The above line works perfectly fine, but when I change the table name
> > > argument (tblTest) to the name of a query, it fails and gives the
> > > following error:
> > >
> > > Runtime error 3275
> > > Unexpected error from external database driver (22).
> > >
> > > The help file with Access 2000 states that it is possible to use a
> > > query instead of a table name, but a sql statement cannot be used - it
> > > must first be saved in a query. I'm not using an sql statement, but
> > > rather a saved query, and I still get the error. According to the
> > > help file this should be working:
> > >
> > > DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel2000,
> > > "qryTest", pathAndFilename , True
> > >
> > > Anybody have any ideas? Thanks in advance.[/color]
> >
> > Do you have a small lookup table to make a test on? One that has 3 or 4
> > records? Create a query on that table. A real simple one. Ex:
> > Select ID, Description From LookupTable;
> > Does it work on your machine?
> >
> > If so, then there is something in your query that is causing a problem.
> > Does you query have subqueries in it? Does it have have a function
> > call...ex NZ(), CINT(), or custom functions you have written? Is it a
> > Totals query where you are grouping the results? Do you have
> > calculated fields in it? IOW, is the query a simple select query or is
> > it a complex query?
> >
> > It should work using your syntax. Finding out if it works with a real
> > simple query would be the first step to check. If not, start breaking
> > your query down (make a copy of the originial) and start removing
> > columns until it finally works.[/color][/color]





----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---