Creating table based on a query

[JoshuaAnthony]

Hi,

I'm exporting the recordSource of a form to Excel. The recordsource itself is a query but I'd like it to remain unchanged the whole time I have the form open (so that it mirrors the data that I export to excel). So ideally I would like to be able to create a table based on the recordSource query, and set that table to be the new recordSource before exporting the data to Excel. My only problem is with actually creating the table.

I have tried:

Dim querydef As Object ' For referring to Access query objects
Dim tabledef As Object

strUserQueryName = [Forms]![User Look-up]![Username] & "Query"
strUserTableName = [Forms]![User Look-up]![Username] & "Table"

Set querydef = CurrentDb.CreateQueryDef(strUserQueryName, strSQL)
Set tabledef = CurrentDb.CreateTableDef(strUserTableName, , strUserQueryName)

' Output a snapshot of the query
DoCmd.OutputTo acOutputTable, strUserTableName, acFormatXLS, strUserFileName

Now, I get a run-time error '3011' on DoCmd.OutputTo saying that it can't find the table that I have specified and when I look in tables it doesn't exist but why does the Set tabledef line work (I imagine it doesn't work because I'm creating the tabledef using a query instead of a table but I can't see any other way)?? Does anybody know how you are ACTUALLY supposed to do this?

Thanks,

Joshua

 

[dcx693]

The recordsource itself is a query but I'd like it to remain unchanged the whole time I have the form open

Why not use the form properties to set the Allow Edit, Additions, and Deletions to No? Then just do a TransferSpreadsheet?:

Me.AllowAdditions = False
Me.AllowEdits = False
Me.AllowDeletions = False
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel97,"queryname"
Me.AllowAdditions = True
Me.AllowEdits = True
Me.AllowDeletions = True

 

[JoshuaAnthony]

Why not use the form properties to set the Allow Edit, Additions, and Deletions to No? Then just do a TransferSpreadsheet?:

Me.AllowAdditions = False
Me.AllowEdits = False
Me.AllowDeletions = False
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel97,"queryname"
Me.AllowAdditions = True
Me.AllowEdits = True
Me.AllowDeletions = True

Thanks!

Now that I think about it, I may have been a little overzealous. Under what conditions would the recordSource requery anyway? I only open the form as a pop-up from the main menu (where it inherits the recordSource) , I don't open any other forms, and just want the recordSource to remain constant while I have the pop-up form open. I think I will add in your idea just to make sure but does the scope of Me.AllowAdditions (, etc.) cover the rowSource of controls on the form? (I would like it to).

 

[dcx693]

does the scope of Me.AllowAdditions (, etc.) cover the rowSource of controls on the form? (I would like it to).

I haven't tested it, but I doubt it. It just stops you from altering records, not making programatica changes to objects on the form.

 

[JoshuaAnthony]

I haven't tested it, but I doubt it. It just stops you from altering records, not making programatica changes to objects on the form.

Sorry, what I meant was that if the recordSource of the form and the rowSource of a control of the form are based on a query, and you set add/edit/delete to false, then does that mean that neither will change until I reset the add/edit/delete properties to true?

Just tested it and setting those properties that you mentioned to false doesn't prevent the record data from changing (i.e showing up as #deleted if I go to that record!!!!). I imagine this is because I am not altering any records within this actual form. I'm just displaying a query...

Thank you for the suggestion though!

 

[raskew]

(I imagine it doesn't work because I'm creating the tabledef using a query instead of a table but I can't see any other way)

If it remains important, why not turn your select query into a make-table query, either at the query grid or in code? The Select query that looks like this:

SELECT  DISTINCT
    Customers.CustomerID
  , Customers.CompanyName
  , Customers.City
  , Customers.Country
FROM
   Customers 
RIGHT JOIN
   Orders 
ON
   Customers.CustomerID = Orders.CustomerID
WHERE
   (((Orders.OrderDate) Between #1/1/1995# 
AND
   #12/31/1995#));

....will look like this as a make-table query

SELECT  DISTINCT
    Customers.CustomerID
  , Customers.CompanyName
  , Customers.City
  , Customers.Country 
INTO
   mynewtable
FROM
   Customers 
RIGHT JOIN
   Orders 
ON
   Customers.CustomerID = Orders.CustomerID
WHERE
   (((Orders.OrderDate) Between #1/1/1995# 
AND
   #12/31/1995#));

...all that's been added is the INTO statement

Bob

 

[JoshuaAnthony]

Hi Bob,

I seem to be having a little trouble with the make-table query.

I have the following three queries:

1.

SELECT [Current].[NCR Number], [Current].[Line Stopper?], [Current].[Date Required], [Current].[Date Raised]
FROM [Current]
WHERE ((([Current].Location)="Some Location"));

2.

SELECT [Current].*
FROM [Current]
WHERE ((([Current].Program)=[Forms]![Main Page]![Program]));

3.

SELECT [Current].*
FROM [Current]
WHERE ((([Current].[NCR Number])=[Forms]![Main Page]![String 1] Or ([Current].[NCR Number])=[Forms]![Main Page]![String 2] _
Or ([Current].[NCR Number])=[Forms]![Main Page]![String 3] Or ([Current].[NCR Number])=[Forms]![Main Page]![String 4] _
Or ([Current].[NCR Number])=[Forms]![Main Page]![String 5] Or ([Current].[NCR Number])=[Forms]![Main Page]![String 6] _
Or ([Current].[NCR Number])=[Forms]![Main Page]![String 7] Or ([Current].[NCR Number])=[Forms]![Main Page]![String 8] _
Or ([Current].[NCR Number])=[Forms]![Main Page]![String 9] Or ([Current].[NCR Number])=[Forms]![Main Page]![String 10]));

Now one of these is chosen as a dynamic record source when a particular pop-up, modal form is selected from the main page.

When I manually create "make-table queries" out of these (with the referenced "Main Page" open), the appropriate table is created correctly (Sweet!).

Unfortunately when I try to the same in my pop-up modal form (even with "Main Page" open in the background), 2 & 3 give me errors when I try CurrentDB.Execute strSQL (1 works fine).

Error for 2.:
"Run-time Error '3061': Too few parameters. Expected 1":

Error for 3.:
"Run-time Error '3061': Too few parameters. Expected 10":

Any ideas how to get around this??

I think if I have to I will export the query to an Excel table and then back to Access as a table there but I imagine that would take much more processing time so I'd prefer not to do that!

Thanks,

Joshua

 

[raskew]

Joshua -

Suspect that it's the modal property that's causing you problems. Try setting modal to No and see if the problem doesn't disappear.

Bob

 

[JoshuaAnthony]

Joshua -

Suspect that it's the modal property that's causing you problems. Try setting modal to No and see if the problem doesn't disappear.

Bob

Hi Bob,

Even tried execution directly from the "Main Page" form which isn't pop-up OR modal and the same problem occurred