473,320 Members | 1,724 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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

Similar topics

2
by: Edwinah63 | last post by:
Hi Everyone, All the very best for 2004!! i need urgent help with this problem, the users are about to skin me alive!! we have an access front end with linked to sql server 2k tables. ...
1
by: Jenny | last post by:
Need urgent help for an unsolved problem. In our ASP web application, we creat a Back button and if user click on this button, it execute history.go(-1) to go back to the previous page. All our...
8
by: Tim::.. | last post by:
Can someone please tell me why I keep getting the following error for some of my web application users but not others??? Even though the application runs from a central webserver??? Thanks for...
6
by: varkey.mathew | last post by:
Dear all, Bear with me, a poor newbie(atleast in AD).. I have to authenticate a user ID and password for a user as a valid Active Directory user or not. I have created the IsAuthenticated...
7
by: zeyais | last post by:
Here is my HTML: <style> ..leftcolumn{float:left;width:300px;border: 1px solid #ccc} ..rtcolumn{float:left;width:600px;border: 1px solid #ccc} </style> <body> <div class="leftcolumn"...
0
by: bprasanth_20 | last post by:
Hi, I need an urgent help. I need to create a UDF (User Defined Function) in DB2 SQL which can accept any number of arguments (from 2 to 5 arguments). I do not how to achieve this. When I pass 2...
1
by: dasilva109 | last post by:
Hi guys I am new to C++ and need urgent help with this part of my code for a uni coursework I have to submit by Thursday //ClientData.h #ifndef CLIENTDATA_H #define CLIENTDATA_H #include...
1
by: Domini | last post by:
Hi all, I need urgent help with an xml issue. Let me explain the scenario: My VB.NET app needs to read an xml (e.g. Sales Orders). This xml contains say 10 sales orders. Next the app needs to split...
0
by: Christopher | last post by:
Urgent Help Needed: The EPVH-1.1 Visual Hull Library. Dear All, I am a student doing research in computer vision. The EPVH-1.1 Visual Hull Library will really help a lot in my research. I...
5
by: koonda | last post by:
Hi all, I am a student and I have a project due 20th of this month, I mean May 20, 2007 after 8 days. The project is about creating a Connect Four Game. I have found some code examples on the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.