DataBase - Database Error for Access 2007

Asked By Jenn
04-Feb-10 05:27 PM
When running a macro (in Access 2007) that???s updating a query and making a
table, the following error occurs:

???Cannot open database ???query name???. It may not be a database that your
application recognizes, or the file may be corrupt???.

The macro is located on the main form of the database. When the database is
closed, reopened and the macro is reran, the error does not occur.  This
database is located on a network drive.   Does anyone know what would cause
this issue? And why it would occur and then not occur once the database is
reopened?

Any thoughts or suggestions on this issue would be greatly appreciated.

Thank you in advance for your assistance.

Jenn
Access Discussions
 
DoCmd.OpenQuery
(1)
Access 2007
(1)
Database
(1)
StDocName
(1)
GoTo
(1)
Expenditures
(1)
Receipts
(1)
AcNormal
(1)
  Daryl S replied to Jenn
05-Feb-10 11:54 AM
Jenn -

Can you post the code for the macro?  You can also make a copy of your
database and convert your macro to code, which will allow you to step through
it and maybe find the error there...

--
Daryl S
  Jenn replied to Daryl S
08-Feb-10 04:44 PM
Daryl,

Here is the code for the Query and the Macro.  Let me know your thoughts.
Thank you for your assistance.

SQL for Query:

SELECT "" AS [Payment Source], [Test GRANT ACTIVITY TABLE].EXP_TR_CODE,
[Test GRANT ACTIVITY TABLE].EXP_TR_NUM_AGY, [Test GRANT ACTIVITY
TABLE].EXP_TR_NUM_NUM, [Test GRANT ACTIVITY TABLE].FUND, [Test GRANT ACTIVITY
TABLE].FED_AID_CAT_NUM, [Test GRANT ACTIVITY TABLE].AGENCY, [Test GRANT
ACTIVITY TABLE].ACTIVITY, [Test GRANT ACTIVITY TABLE].OBJECT, [Test GRANT
ACTIVITY TABLE].SUB_OBJECT, [Test GRANT ACTIVITY TABLE].REPORTING_ORG_2,
[Test GRANT ACTIVITY TABLE].Comments, [Test GRANT ACTIVITY
TABLE].FED_AID_NUM, [Test GRANT ACTIVITY TABLE].Year, [Test GRANT ACTIVITY
TABLE].Month, [Test GRANT ACTIVITY TABLE].ACCEPTANCE_DATE, [Test GRANT
ACTIVITY TABLE].FED_AID_STATUS, [Test GRANT ACTIVITY TABLE].BUDGET_OBJ_CLS,
[Test GRANT ACTIVITY TABLE].EXPENDED_AMT,
IIf([Comments]="NETWORK",[EXPENDED_AMT],0) AS Network,
IIf([Comments]="PS",[EXPENDED_AMT],0) AS [Personal Services],
IIf([Comments]="EE",[EXPENDED_AMT],0) AS [E & E],
IIf([Comments]="FRINGE",[EXPENDED_AMT],0) AS Fringe,
IIf([Comments]="REFUND",[EXPENDED_AMT],0) AS Refunds,
IIf([Comments]="REVENUE",[EXPENDED_AMT],0) AS Revenue,
IIf([Comments]="INDIRECT",[EXPENDED_AMT],0) AS Indirect,
IIf([Comments]="WAN",[EXPENDED_AMT],0) AS Wan, 0 AS Allocations,
IIf([Comments]="",[EXPENDED_AMT],0) AS [Misc Other],
[Fringe]+[Indirect]+[Network]+[Wan]+[E & E]+[Personal Services] AS Total, ""
AS [Manual Adjustment], 0 AS [Revenue Adj], 0 AS [Refund Adj], 0 AS [Total
Receipt Adj], 0 AS [Personal Services Adj], 0 AS [Fringe Adj], 0 AS [Indirect
Adj], 0 AS [Other Expenditures Adj], 0 AS [Network Adj], 0 AS [WAN Adj], 0 AS
[Allocations Adj], 0 AS [Total Exp Adj], 0 AS [Total Receipts] INTO [Test
Grant Activity Reformat Table]
FROM [Test GRANT ACTIVITY TABLE];


Visual Basic code for button:

Private Sub Run_Grant_Reformat_Query_Click()
On Error GoTo Err_Run_Grant_Reformat_Query_Click

Dim stDocName As String

stDocName = "Test Grant Activity Reformat Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Run_Grant_Reformat_Query_Click:
Exit Sub

Err_Run_Grant_Reformat_Query_Click:
MsgBox Err.Description
Resume Exit_Run_Grant_Reformat_Query_Click

End Sub
Previous Discussion:  update form on change of field used in expression
Create New Account
help
Roger Jennings on Access / Sharepoint DataBase http: / / www.quepublishing.com / articles / article.aspx?p = 1606238 I was very dismayed reading this is it makes it pretty clear that in-house hosting of your Sharepoint server with Access Services to support browser-based Access apps is something only large companies will be able to afford, because the pricing and licensing for the Enterprise version is very, very steep. The alternative is hosted Sharepoint / Access Services, and the costs do not seem terribly high. It seems to me that the pricing is upside-down. Big enterprises do not want to deploy Access apps in the browser - - they will build their own .NET apps, browser-based or not 2010, it could vastly increase those costs for organizations with legacy hardware). The features of Access 2010 used in conjunction with Sharepoint 2010 seem to me to be most compelling for and small businesses. I do not know if I could sell clients on hosted Sharepoint / Access. I have been able to sell clients on hosted Exchange, and it is been very
Library Database DataBase Firstly, I am not sure if Library database is the correct term. I am talking about Dbs to which you set a reference drew blanks last time. Now assuming I have several referenced (library?) databases in my main database, how do I get the names of all the forms in all the Dbs? CodeProject AllForms.Count gives me the correct number of forms if I am in that referenced database. What I think I need is a method of looping through all the different CodeProject names in the main database, but there is not a sort of CodeProjects parent object. Or somehow moving the focus in the library and seeing how many forms there are. Any help gratefully received Phil Access Discussions David W. Fenton (1) Access 2007 (1) Office 2007 (1) Windows 7 (1) Distributed (1) Exception (1) Relative (1) Declare
Might be outgrowing Access but daunted by SQL Server DataBase I am close to completing the consolidation of various small Access databases and a couple of Excel spreadsheets that my little company uses (5 staff) into an all encompasing Access database and I was planning on splitting the database when I was finished to allow simaltaneous use of it by staff on our small more and more obvious to me that I am going to need to give staff access to this new database from remote locations (outside of our office network). Two staff members are regularly overseas, one
requery but record ON not getting updated DataBase I have A FORM THAT SHOW ALL JOBS - WE check off(yes / no) which jobs have it cleared without have to come out and back into the form??? thanks, barb Access Discussions JeffJobsToBEATqry (1) JeffCurrentJobsClearTobeAt (1) CmdResetJobList (1) Database (1) CmdjustjobstobeAT (1) StLinkCriteria (1) StDocName (1) CrossOut (1) You need to SAVE the changes to the record before you requery you will have to figure out where that line belongs in your routine. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Here is my code below - for one record always Still has a check box??? thanks, barb Private Sub cmdResetJobList_Click() On Error GoTo Err_cmdResetJobList_Click Dim stDocName As String stDocName = "JeffCurrentJobsClearTobeAt&CrossOut" DoCmd.OpenQuery stDocName, acNormal, acEdit Exit_cmdResetJobList_Click: Exit Sub Err_cmdResetJobList_Click: MsgBox
Is Null in a Report DataBase I am using a list box to open a report for a specific record. I way to get this accomplished. Any help would be appreciated. Private Sub List14_AfterUpdate() On Error GoTo Err_List14_Click Dim stDocName As String Dim stDocName2 As String Dim stLinkCriteria As String Dim stErrSiteNull As String stDocName = "rptRebandingEquipFreq" stDocName2 = "qryRebandingEquipFreq" stErrSiteNull = "No Feeder System Set Yet, Try again Later!" 'open report Query and verify that there is data, if null close query, message box and exit DoCmd.OpenQuery stDocName2, acViewNormal If IsNull(rs.Fields("SiteNum")) Then DoCmd.Close MsgBox stErrSiteNull, vbOKOnly, "Dude!" GoTo Exit_List14_Click Else 'If the query is not null close query and open report DoCmd.Close DoCmd.OpenReport stDocName, acViewPreview End If Exit_List14_Click: Exit Sub Err_List14_Click: MsgBox Err.Description Resume Exit_List14_Click End Sub Access