Renaming recordsets

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Hi

I have created a recordset and when I call for rs.name, the syntax appears
as the name.

How can I rename the recordset to what I prefer.

Thanks in advance

Richard
 
Richard said:
Hi

I have created a recordset and when I call for rs.name, the syntax
appears as the name.

How can I rename the recordset to what I prefer.
Click to expand...

You can't -- a recordset's Name property is read-only. It will be up to
the first 256 characters of whatever table, query, or SQL statement is
used to open the recordset. Why would you want to do this, anyway?
What are you trying to do?
 
Hi Dirk

I'm trying to open a recordset, name it, save it and export it to excel. I
need to record what has been sent out to Excel.

I am unable even to send it to Excel, can we do it through a recordset? Now,
what I do is open a saved query and output it to Excel.

Thanks again
Richard
 
Richard said:
Hi Dirk

I'm trying to open a recordset, name it, save it and export it to
excel. I need to record what has been sent out to Excel.

I am unable even to send it to Excel, can we do it through a
recordset? Now, what I do is open a saved query and output it to
Excel.
Click to expand...

I'm sorry, I still don't follow. A "saved recordset" would be a table!
A recordset is just an in-memory representation of a set of records,
which may or may not be tied (closely or loosely) to a specific table or
query. But maybe I'm taking a complicated view of something that is
actually much simpler. Where is this "recordset" coming from? Are you
building a SQL statement on the fly, based on user input, or are you
maybe opening a saved query that picks up criteria from a form? Is it
that you want to assign a name to the specific query *with its criteria*
that is being exported to Excel?

You can use the DoCmd.TransferSpreadsheet method to export a table or
saved query directly to Excel without opening it first. But I think if
I had the bigger picture of what you're trying to do, I could make a
much more detailed suggestion. Could you give me some background?
 
Hi Dirk

I am actually building a SQL statement on the fly, based on user input from
a form. I won't need to save the records, just the Filename which will
consist of what records was exported ot Excel. For eg. Filename "QryA-C"
will mean that on that particular export Companies beginning with A to C was
sent the particular materials. (for marketing purposes)

Here's the underlying code:

Dim DB As DAO.database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT wbdata.DB, company.COMPANY FROM ......."

Set DB = CurrentDb
Set rs = DB.OpenRecordset(strSQL)

From here I would like to create a name for the recordset and then export
the recordset to Excel, close the recordset, save the name to the
MarketingRecordsTable.

Hope I explained clearly enough. Presently, what I do is create a saved
query instead of, as above, open the query, output it to excel, close the
query. Concatenate the file name from criteria fields to the FileName field
and save to the table, which is fine, but I just need to find out if it can
be done through code with the above query.

Thanks again for your time and patience.

Richard
 
Hi Dirk

I tried doing what you suggested using the DoCmd.TransferSpreadsheet method.
It doesn't work. Would you mind taking a look at the syntax?

DoCmd.TransferSpreadsheet acExport, , strFileName, strFileName

I have renamed the query to strfilename with this code

DoCmd.Rename strFileName, acQuery, "extract1"

Many thanks
Richard
 
Richard said:
Hi Dirk

I tried doing what you suggested using the DoCmd.TransferSpreadsheet
method. It doesn't work. Would you mind taking a look at the syntax?

DoCmd.TransferSpreadsheet acExport, , strFileName, strFileName

I have renamed the query to strfilename with this code

DoCmd.Rename strFileName, acQuery, "extract1"
Click to expand...

The syntax works fine for me. I just executed this in my own Immediate
Window:

DoCmd.TransferSpreadsheet acExport, , "qryDates", "qryDates"

-- qryDates being a query in my test database -- and it worked fine,
creating file "qryDates.XLS" in my "My Documents" folder. In what way
did that "not work" for you? Of course, you may want to specify the
complete path for the output file, but in principle it should work as
you've written it.
 
Richard said:
Hi Dirk

I am actually building a SQL statement on the fly, based on user
input from a form. I won't need to save the records, just the
Filename which will consist of what records was exported ot Excel.
For eg. Filename "QryA-C" will mean that on that particular export
Companies beginning with A to C was sent the particular materials.
(for marketing purposes)

Here's the underlying code:

Dim DB As DAO.database
Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT wbdata.DB, company.COMPANY FROM ......."

Set DB = CurrentDb
Set rs = DB.OpenRecordset(strSQL)

From here I would like to create a name for the recordset and then
export the recordset to Excel, close the recordset, save the name to
the MarketingRecordsTable.

Hope I explained clearly enough. Presently, what I do is create a
saved query instead of, as above, open the query, output it to excel,
close the query. Concatenate the file name from criteria fields to
the FileName field and save to the table, which is fine, but I just
need to find out if it can be done through code with the above query.

Thanks again for your time and patience.

Richard
Click to expand...

I think I would do this by having a saved querydef named (maybe)
"qryExport", the SQL of which I would change immediately before
exporting. I'll assume you have reached a stage in your processing
where the variable "strSQL" contains the SELECT statement for the
records to be exported, and "strExportName" contains the "name" you've
assigned to this particular export. From that point, your code might
look something like this:

DB.QueryDefs("qryExport").SQL = strSQL

DoCmd.TransferSpreadsheet acExport, , "qryExport", strExportName

DB.Execute _
"INSERT INTO MarketingRecordTable (FileName) " & _
"VALUES(" & Chr(34) & strExportName & Chr(34) & ")", _
dbFailOnError

You could, if you wished, also save the SQL that was executed in the
MarketingRecordTable, if you had a field in that table for the purpose.
 
Hi Dirk

Actually it worked. I was waiting for it to open up to Excel. I checked the
mdb folder and there was all the files I transfered..hehe

Is there a way for it to open when I transfer? I need to save it as a .db
file.

Thanks alot for your help.

Richard
 
HI Dirk

Better still, if I can specify a path to save and save it as a ".dbf" file,
the better.

any suggestions

Thanks again
Richard
 
Richard said:
Hi Dirk

Actually it worked. I was waiting for it to open up to Excel. I
checked the mdb folder and there was all the files I transfered..hehe

Is there a way for it to open when I transfer? I need to save it as a
.db file.
Click to expand...

You could use DoCmd.OutputTo (you'll find it in the help file) instead
of TransferSpreadsheet, and then use that method's "AutoStart" argument
to cause Excel to open the exported file. One drawback to this is that
OutputTo gives you no choice as to the specific Excel file-format used,
and I think it uses an older, more limited file format.

Another option is to export to the file using TransferSpreadsheet, and
then either use

Application.FollowHyperlink <path and name of exported .xls file>

to open it -- or call the ShellExecute API -- or else use automation to
create an Excel application object and tell that object to open the
spreadsheet file. If the whole purpose of opening the file in Excel is
to convert it to a .db (.dbf?) file, you would probably want to go the
route of automating Excel, so there would be no need for the user to
actually work with Excel manually at all.

On the other hand, if your whole purpose is to come up with this .db
file, I think you should be able to use TransferDatabase to do it in one
go, instead of TransferSpreadsheet followed by some manipulation of
Excel. What specific format are you targeting? This works for me:

DoCmd.TransferDatabase acExport, "dBase IV", _
"C:\Temp", acQuery, "qryTest", "qryTest.dbf"
 
Dirk Goldgar said:
The syntax works fine for me. I just executed this in my own Immediate
Window:

DoCmd.TransferSpreadsheet acExport, , "qryDates", "qryDates"

-- qryDates being a query in my test database -- and it worked fine,
creating file "qryDates.XLS" in my "My Documents" folder. In what way
did that "not work" for you? Of course, you may want to specify the
complete path for the output file, but in principle it should work as
you've written it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top