Transferspreadsheet Access 2003 and 2007

A

ahabrendan

I am going to try and explain this in detail.
I have a database I created in access 2003.
This database pulls in text files and then reformats the files and
splits them.
It splits them by creating queries via vba code for export.
The query is exatcly the same fields for each of the pieces of data it
needs to export.
Althought the criteria is a little different for each piece.
I then execute the statement docmd.transferspreadsheet qdf.name
It does this for all of the files that I need to export.
Although it stops with one of the queries and I get a parameter popup
window.
If I debug and try to run that line of code I get an error 3207
reserved error.


Now this worked for over 2 years in 2003.
We recently upgraded all of the users of this database except me to
2007.
This also worked for a few months before I started getting this
problem three days ago.
Now this database is setup as follows:
I have a front end and a backend.
I have left the backend database in 2003, and the frontend is located
on the server as a 2003 database.
The user of the database runs a batch file that will always copy of
the database from the server and then starts it.
So basically the user is running a 2003 database from 2007.
Like I said this all worked up until three days ago. (from 2003 and
2007)

Any help would be greatly apreciated. TIA
 
J

Jerry Whittle

What is the SQL of the query where it stops and what is the parameter looking
for?
 
A

ahabrendan

What is the SQL of the query where it stops and what is the parameter looking
for?
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder







- Show quoted text -

That is the sql for the query that I create through VBA code.
Select * from qryAHAPrePendsBusinessAreaAssignment where BusinessArea
= 99 order by ParentGroupNumber, ReceiveDate

after the query is created I try to run and get the error:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdf.Name,
FileName, True

There are no parameters in this query at all.
 
A

ahabrendan

That is the sql for the query that I create through VBA code.
Select * from qryAHAPrePendsBusinessAreaAssignment where BusinessArea
= 99 order by ParentGroupNumber, ReceiveDate

after the query is created I try to run and get the error:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdf.Name,
FileName, True

There are no parameters in this query at all.- Hide quoted text -

- Show quoted text -

I should also say that I can output this with OutputTo but I need to
keep it transferspreadsheet because some users are still using 2003.
 
J

Jerry Whittle

What does the parameter popup ask for?

What happens if you run the SQL statement that you posted below as a stored
query?

As you are doing a query on a query, could the underlying query be the
problem?

As you are creating the query in code, could there be something wrong with
that part of the code?
 
A

ahabrendan

What does the parameter popup ask for?

What happens if you run the SQL statement that you posted below as a stored
query?

As you are doing a query on a query, could the underlying query be the
problem?

As you are creating the query in code, could there be something wrong with
that part of the code?
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder






- Show quoted text -

The parameter shows part of a field name, it is cutoff.

I do take the sql string that I use and create a query from it and
that runs fine.

The underlying query is fine. It is only a query that places the data
into a certain field order.

I am creating the querydef in vba but again that used to work and now
it doesn't. It ran fine in both 2003 and 2007.
And nothing has changed in this code. I was wondering if there was an
update within the last two weeks.
I am also creating queries using the underlying query, I have a field
that is called business area and the underlying query just formats the
data into what the business area needs to see.
The query that I create in code just selects the data based on the
different business areas.
All of the business areas use this same code and works correctly for
all of them but one of them.
Now I stopped the code where I am doing the docmd.transferspreadsheet
and checked the query and that looks fine.
I also inserted a line of code there that uses the DoCmd.OutputTo and
works fine.
I am not sure what has changed but something has definitely changed
with the transferspreadsheet function.
 
F

fez

The parameter shows part of a field name, it is cutoff.

I do take the sql string that I use and create a query from it and
that runs fine.

The underlying query is fine. It is only a query that places the data
into a certain field order.

I am creating the querydef in vba but again that used to work and now
it doesn't. It ran fine in both 2003 and 2007.
And nothing has changed in this code. I was wondering if there was an
update within the last two weeks.
I am also creating queries using the underlying query, I have a field
that is called business area and the underlying query just formats the
data into what the business area needs to see.
The query that I create in code just selects the data based on the
different business areas.
All of the business areas use this same code and workscorrectlyfor
all of them but one of them.
Now I stopped the code where I am doing the docmd.transferspreadsheet
and checked the query and that looks fine.
I also inserted a line of code there that uses the DoCmd.OutputTo and
works fine.
I amnotsure what has changed but something has definitely changed
with thetransferspreadsheetfunction.- Hide quoted text -

- Show quoted text -

I have found a solution to this that works.
I put an If statement where the DoCmd.TransferSpreadsheet was.
If the Application.Version = 12 then
DoCmd.OutputTo ...
ElseIf Application.Version = 11 Then
DoCmd.TransferSpreadsheet...
End If

I checked that in both versions and it seems to work.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top