Hello, I'm getting an error with a Docmd.Transferspreadsheet line of
code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000,
"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.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000,
"qryTest", pathAndFilename, True
Anybody have any ideas? Thanks in advance. 3 11012
user_5701 wrote: Hello, I'm getting an error with a Docmd.Transferspreadsheet line of code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000, "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.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000, "qryTest", pathAndFilename, True
Anybody have any ideas? Thanks in advance.
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.
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 transferspreadsheet
command.
Does anyone know why this is?
Salad <oi*@vinegar.com> wrote in message news:<FM****************@newsread3.news.pas.earthl ink.net>... user_5701 wrote: Hello, I'm getting an error with a Docmd.Transferspreadsheet line of code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000, "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.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000, "qryTest", pathAndFilename, True
Anybody have any ideas? Thanks in advance.
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.
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 TransferSpreadsheet 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" <us*******@hotmail.com> wrote in message
news:f1**************************@posting.google.c om... 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 transferspreadsheet command.
Does anyone know why this is?
Salad <oi*@vinegar.com> wrote in message
news:<FM****************@newsread3.news.pas.earthl ink.net>... user_5701 wrote: Hello, I'm getting an error with a Docmd.Transferspreadsheet line of code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000, "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.TransferSpreadsheet acExport, acSpreadsheetTypeExcel2000, "qryTest", pathAndFilename, True
Anybody have any ideas? Thanks in advance.
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.
----== 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 =--- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: mcnewsxp |
last post by:
how can i specify a particular worksheet using the docmd
transferspreadsheet?
thanks,
mcnewsxp
|
by: Squirrel |
last post by:
I have a command button on a subform to delete a record.
The only statement in the subroutine is:
DoCmd.RunCommand acCmdDeleteRecord
The subform's recordsource is "select * from tblVisit order...
|
by: aleksandra_83 |
last post by:
Hello,
I have searched all over google groups and internet, but I have not
found a solution to this problem that has worked. The solutions I found
helped me single out the line that is causing...
|
by: Tom Louchbaum |
last post by:
I try to use this command to import a spreadsheet
"DoCmd.TransferSpreadsheet acImport, 8, tablename & " -tbl", DIR &
"Book2.xls", True"
and it errors out. If I try to import that same...
|
by: MLH |
last post by:
Running the following procedure, Access 97 is giving me a 2465 error
in line #250. Anyone know why? MySQL is a global var in the app. This
is running in frmVehicleChooserForm.
Private Sub...
|
by: Tomba |
last post by:
I have a really annoying problem, which i tried solving for almost 3 days now. (I googled in any variation i could think of, but i couldn't find anything that will help my stupid me understand:\)...
|
by: sranilp |
last post by:
Hey All,
Actually I need to export the data from Access to Excel particular spreadsheet(ie.Raw Data),so I was using Docmd.Transferspreadsheet but in this syntax where i can give the spreadsheet...
|
by: RZ15 |
last post by:
Hi,
I frequently have code in my forms that will turn warnings off, run make table queries, then turn warnings back on using the following code:
DoCmd.SetWarnings WarningsOff
DoCmd.OpenQuery...
|
by: amir369 |
last post by:
Hi All,
I'm trying to import an Excel sheet, but with more the one rang, couse the columns aint sequence.
for example:
DoCmd.TransferSpreadsheet acImport, , "DailyRprzntvRpt", _
...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |