469,955 Members | 2,494 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,955 developers. It's quick & easy.

Urgent VB SQL help!!!

Looking for some help urgently with some VB SQL Access stuff. You'll have to excuse the messiness of the code, I'm a newbie at all this. I'm running this code with a selection of check boxes to select extra fields for my table that is then exported to Excel. there are 3 fields that are always part of the SQL, when these are selected it works fine, however when I select any of the optional fields I get a Runtime Error '3011' saying:

the Microsoft Jet database engine could not find the object "

and that's it, no other clues at all as to where the problem could be in the code and I'm stuck! Any help would be greatly appreciated.

Code follows:

Private Sub lblEC_Click()

Dim EC As ADODB.Connection
Set EC = CurrentProject.Connection

'Dim ECRecordSet As New ADODB.Recordset
'ECRecordSet.ActiveConnection = EC

Dim x
Dim TABLESQL As String
Dim ECSQL As String

'DoCmd.Hourglass True

On Error Resume Next
DoCmd.DeleteObject acTable, "tblResults"
On Error GoTo 0

'Table creation SQL

TABLESQL = "CREATE TABLE tblResults ([Date] DATETIME, [ProductionVolume] LONG, [Usage] LONG"
If chkBudgetUsage Then TABLESQL = TABLESQL & ", [BudgetUsage] LONG"
If chkActualCost Then TABLESQL = TABLESQL & ", [ActualCost] CURRENCY"
If chkBudgetCost Then TABLESQL = TABLESQL & ", [BudgetCost] CURRENCY"
TABLESQL = TABLESQL & ") "
CurrentDb.Execute TABLESQL

'Query SQL

ECSQL = "INSERT INTO tblResults ([Date], [ProductionVolume], [Usage]"
If chkBudgetUsage Then ECSQL = ECSQL & ", [BudgetUsage]"
If chkActualCost Then ECSQL = ECSQL & ", [ActualCost]"
If chkBudgetCost Then ECSQL = ECSQL & ", [BudgetCost]"
ECSQL = ECSQL & ") "
ECSQL = ECSQL & "SELECT DISTINCT tblAccounting.Date, tblProduction.ProductionVolume, Sum(tblReading.ReadingVolume) AS [Usage]"
If chkBudgetUsage Then ECSQL = ECSQL & ", tblBudget.Budget AS BudgetUsage "

'Else
' ECSQL = ECSQL & ", 0 AS BudgetUsage "
'End If

If chkActualCost Then ECSQL = ECSQL & ", Sum([tblreading].[readingvolume]*[tbltariff].[tariff]) AS ActualCost"

'Else
' ECSQL = ECSQL & ", 0 as ActualCost "
'End If

If chkBudgetCost Then ECSQL = ECSQL & ", Sum([tblbudget].[budget]*[tblTariff].[Tariff]) AS BudgetCost"

'Else
' ECSQL = ECSQL & ", 0 as BudgetCost"
'End If

ECSQL = ECSQL & "FROM tblUtility INNER JOIN ((tblPeriod INNER JOIN ((tblAccounting INNER JOIN (tblProduction INNER JOIN tblBudget ON tblProduction.DepartmentID = tblBudget.DepartmentID) ON (tblAccounting.Date = tblProduction.Date) AND (tblAccounting.Date = tblBudget.Date)) INNER JOIN (tblReading INNER JOIN tblMeter ON tblReading.MeterID = tblMeter.MeterID) ON tblAccounting.Date = tblReading.Date) ON tblPeriod.Period = tblAccounting.Period) INNER JOIN tblTariff ON tblPeriod.Period = tblTariff.Period) ON (tblUtility.UtilityID = tblTariff.UtilityID) AND (tblUtility.UtilityID = tblMeter.UtilityID) AND (tblUtility.UtilityID = tblBudget.UtilityID) "

Select Case Frame40
Case 1
'last 7 days
ECSQL = ECSQL & "GROUP BY tblAccounting.Date, tblProduction.ProductionVolume, tblBudget.Budget, tblProduction.DepartmentID, tblUtility.UtilityID "
ECSQL = ECSQL & "HAVING tblAccounting.Date > #" & Format(Date - 7, "dd/mm/yyyy") & "#"
Case 2
'period to date
ECSQL = ECSQL & "GROUP BY tblAccounting.Date, tblProduction.ProductionVolume, tblBudget.Budget, tblProduction.DepartmentID, tblUtility.UtilityID "
ECSQL = ECSQL & "HAVING tblAccounting.period = " & DLookup("period", "tblAccounting", "date=#" & Format(Date, "dd/mmm/yyyy") & "#")
Case 3
'ytd
ECSQL = ECSQL & "GROUP BY tblAccounting.Date, tblProduction.ProductionVolume, tblBudget.Budget, tblProduction.DepartmentID, tblUtility.UtilityID "
ECSQL = ECSQL & "HAVING right(tblAccounting.period,2) = " & Right(DLookup("period", "tblAccounting", "date=#" & Date & "#"), 2)
Case 4
'date range
ECSQL = ECSQL & "GROUP BY tblAccounting.Date, tblProduction.ProductionVolume, tblBudget.Budget, tblProduction.DepartmentID, tblUtility.UtilityID "
ECSQL = ECSQL & "HAVING tblAccounting.Date BETWEEN #" & txtStart & "# AND #" & txtEnd & "#"
End Select
ECSQL = ECSQL & " AND ((tblProduction.DepartmentID)=1) AND ((tblUtility.UtilityID)=1)"

'ECRecordSet.Open ECSQL

CurrentDb.Execute ECSQL
Dim output As String

output = "d:\mpl\Utilities\Database(2)\Database\Department\ charts.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tblResults", output, False

'x = Shell(output, vbMaximizedFocus)

x = Shell("c:\program files\microsoft office\office11\excel.exe d:\mpl\Utilities\Database(2)\Database\Department\c harts.xls", vbMaximizedFocus)

Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

Set Xl = CreateObject("Excel.application")
Set XlBook = GetObject(output)

Xl.Visible = True
XlBook.Windows(1).Visible = True

1 Set XlSheet = XlBook.Worksheets(1)

'XlSheet.Range("a1").CopyFromRecordset ECRecordSet
'Windows("output.xls").Activate
'Sheets("GraphicalAnalysis").Select

'ECRecordSet.Close

'Xl.Close

Set EC = Nothing
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
Set ECRecordSet = Nothing
Set EC = Nothing
'DoCmd.Hourglass False
End Sub


Exuse the comments and stuff.

A swift response would be appreciated!!

Cheers

JP :confused:
Jul 11 '06 #1
4 3026
sashi
1,754 Expert 1GB
Hi Jonathan,

when you get this "Microsoft Jet database engine could not find the object" error it means one of the the table name is not found in your database.. pls check your line of code again and make sure the correct table name is specified.. good luck my fren.. :)

just make sure that these table names are correct and exist in your SQL database..

tblResults
tblAccounting
tblProduction
tblReading
tblBudget
tblTariff
tblUtility
tblPeriod
tblMeter
Jul 11 '06 #2
Thanks for that, all those tables are in the database and they are all in the SQL too. when the error message comes up it's not for a ", it's for ' ' with no space between them. I've been through the code a number of times now and the only way I can make it work is by doing the following:

outputpath = "d:\mpl\Utilities\Database(2)\Database\Department\ charts.xls"
Sheet = "Results"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tblResults", outputpath, False, (Sheet)

There is one major issue with this though. When the query is run more than 5 or 6 times the same error message appears. Is there a limit or a hidden cache or something that needs to be emptied as part of the code?? Again, a swift response would be appreciated, I'm racking my brain trying to sort this out and it nearly has me beaten!!
Jul 11 '06 #3
sashi
1,754 Expert 1GB
Hi Jonathan,

i see.. it's something to do with the single quote.. well.. let me check your SQL statements line by line.. it's gonna take time buddy.. :)

well.. as far as i know there are no restriction to number of times a particular query can be executed.. let me check that part for you as well.. take care.. have a great day buddy.. :)
Jul 11 '06 #4
You'll have to excuse me, I'm new to all this programming stuff, I've manged to go from having no knowledge at all 4 weeks ago to writing that stuff I posted. I'm getting the hang of it but the basics keep tripping me up, need some good programming etiquette!! Thanks for the help here, really appreciated!!
Jul 11 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Tim::.. | last post: by
7 posts views Thread by zeyais | last post: by
1 post views Thread by dasilva109 | last post: by
reply views Thread by Christopher | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.