Urgent VB SQL help!!!

[JonathanParker]

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.Connectio n
Set EC = CurrentProject. Connection

'Dim ECRecordSet As New ADODB.Recordset
'ECRecordSet.Ac tiveConnection = EC

Dim x
Dim TABLESQL As String
Dim ECSQL As String

'DoCmd.Hourglas s True

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

'Table creation SQL

TABLESQL = "CREATE TABLE tblResults ([Date] DATETIME, [ProductionVolum e] 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.Execu te TABLESQL

'Query SQL

ECSQL = "INSERT INTO tblResults ([Date], [ProductionVolum e], [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.D ate, tblProduction.P roductionVolume , Sum(tblReading. ReadingVolume) AS [Usage]"
If chkBudgetUsage Then ECSQL = ECSQL & ", tblBudget.Budge t 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.D epartmentID = tblBudget.Depar tmentID) ON (tblAccounting. Date = tblProduction.D ate) AND (tblAccounting. Date = tblBudget.Date) ) INNER JOIN (tblReading INNER JOIN tblMeter ON tblReading.Mete rID = tblMeter.MeterI D) ON tblAccounting.D ate = tblReading.Date ) ON tblPeriod.Perio d = tblAccounting.P eriod) INNER JOIN tblTariff ON tblPeriod.Perio d = tblTariff.Perio d) ON (tblUtility.Uti lityID = tblTariff.Utili tyID) AND (tblUtility.Uti lityID = tblMeter.Utilit yID) AND (tblUtility.Uti lityID = tblBudget.Utili tyID) "

Select Case Frame40
Case 1
'last 7 days
ECSQL = ECSQL & "GROUP BY tblAccounting.D ate, tblProduction.P roductionVolume , tblBudget.Budge t, tblProduction.D epartmentID, tblUtility.Util ityID "
ECSQL = ECSQL & "HAVING tblAccounting.D ate > #" & Format(Date - 7, "dd/mm/yyyy") & "#"
Case 2
'period to date
ECSQL = ECSQL & "GROUP BY tblAccounting.D ate, tblProduction.P roductionVolume , tblBudget.Budge t, tblProduction.D epartmentID, tblUtility.Util ityID "
ECSQL = ECSQL & "HAVING tblAccounting.p eriod = " & DLookup("period ", "tblAccounting" , "date=#" & Format(Date, "dd/mmm/yyyy") & "#")
Case 3
'ytd
ECSQL = ECSQL & "GROUP BY tblAccounting.D ate, tblProduction.P roductionVolume , tblBudget.Budge t, tblProduction.D epartmentID, tblUtility.Util ityID "
ECSQL = ECSQL & "HAVING right(tblAccoun ting.period,2) = " & Right(DLookup(" period", "tblAccounting" , "date=#" & Date & "#"), 2)
Case 4
'date range
ECSQL = ECSQL & "GROUP BY tblAccounting.D ate, tblProduction.P roductionVolume , tblBudget.Budge t, tblProduction.D epartmentID, tblUtility.Util ityID "
ECSQL = ECSQL & "HAVING tblAccounting.D ate BETWEEN #" & txtStart & "# AND #" & txtEnd & "#"
End Select
ECSQL = ECSQL & " AND ((tblProduction .DepartmentID)= 1) AND ((tblUtility.Ut ilityID)=1)"

'ECRecordSet.Op en ECSQL

CurrentDb.Execu te ECSQL
Dim output As String

output = "d:\mpl\Utiliti es\Database(2)\ Database\Depart ment\charts.xls "

DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel8, "tblResults ", output, False

'x = Shell(output, vbMaximizedFocu s)

x = Shell("c:\progr am files\microsoft office\office11 \excel.exe d:\mpl\Utilitie s\Database(2)\D atabase\Departm ent\charts.xls" , vbMaximizedFocu s)

Dim Xl As Excel.Applicati on
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

Set Xl = CreateObject("E xcel.applicatio n")
Set XlBook = GetObject(outpu t)

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

1 Set XlSheet = XlBook.Workshee ts(1)

'XlSheet.Range( "a1").CopyFromR ecordset ECRecordSet
'Windows("outpu t.xls").Activat e
'Sheets("Graphi calAnalysis").S elect

'ECRecordSet.Cl ose

'Xl.Close

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


Exuse the comments and stuff.

A swift response would be appreciated!!

Cheers

JP :confused:

[sashi]

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

[JonathanParker]

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\Utiliti es\Database(2)\ Database\Depart ment\charts.xls "
Sheet = "Results"
DoCmd.TransferS preadsheet acExport, acSpreadsheetTy peExcel8, "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!!

[sashi]

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.. :)

[JonathanParker]

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!!