I step through the code, and sometimes the code works, but then again, sometimes the code does not work. The error message I get is that "The Microsoft Jet database engine cannot find the input table or query 'qryPassThroughQuery'. Make sure it exists and that its name is spelled correctly."
I cannot understand it. On one occasion it will accept the record and pass through it without a problem. On another occasion, it does not accept it at all, providing this message. Could somebody please provide some advice???
Why can't the code find the query when the line practically in front of it does just that -- creates the query that it needs in that line???
Private Sub ProcessHeadcountRecords()
Dim Cnxn As ADODB.Connection
Dim strConn As String
Dim rstInputFile As ADODB.Recordset
Dim cmdSQLInputFile As ADODB.Command
Dim strSQLInputFile As String
Dim rstHyperionMany As ADODB.Recordset
Dim cmdSQLHyperionMany As ADODB.Command
Dim strSQLHyperionMany As String
Dim rstHyperionOne As ADODB.Recordset
Dim cmdSQLHyperionOne As ADODB.Command
Dim strSQLHyperionOne As String
Dim rstQueryDef As ADODB.Recordset
Dim strPassThroughQuery As String
Dim strDBPath As String
Dim strFileName As String
Dim strMessage As String
strDBPath = "J:\GELCO DATABASE\Headcount Database\Headcount Database.mdb"
Set dbsHeadcount = OpenDatabase(strDBPath)
Set Cnxn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & strDBPath & ""
Set rstInputFile = New ADODB.Recordset
strSQLInputFile = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _
"[L_R_G], [REGION], [JOB_FUNCTION], [ACTUAL], [NUMINDEX] " & _
rstInputFile.Open strSQLInputFile, Cnxn, adOpenKeyset, adLockOptimistic
Set rstHyperionMany = New ADODB.Recordset
strSQLHyperionMany = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _
"[L_R_G], [REGION], [JOB_FUNCTION], [NUMFOREIGNKEY] " & _
"FROM [TBLHYPERIONMANY2] ORDER BY [NUMFOREIGNKEY]"
rstHyperionMany.Open strSQLHyperionMany, Cnxn, adOpenKeyset, adLockOptimistic
strFileName = "qryPassThroughQuery"
txtMessageBoxText.Text = "Processing all the records from the upload file " & _
"to an updated file to be imported into Hyperion."
Do Until rstInputFile.EOF
strSQLHyperionMany = "SELECT [COUNTRY], [TYPE], " & _
"[BUSINESS_UNIT], [L_R_G], [REGION], [JOB_FUNCTION], [NUMFOREIGNKEY] " & _
"FROM [TBLHYPERIONMANY2] " & _
"WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _
"AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _
"AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _
"AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _
"AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _
"AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'"
Set qdfNew = dbsHeadcount.CreateQueryDef(strFileName, strSQLHyperionMany) // CREATES THE QUERY
Set rstQueryDef = New ADODB.Recordset
strPassThroughQuery = "SELECT * FROM qryPassThroughQuery"
rstQueryDef.Open strPassThroughQuery, Cnxn, adOpenKeyset, adLockOptimistic // NEEDS TO USE THE QUERY ***HERE ***
If rstQueryDef.RecordCount = 1 Then
rstInputFile![NUMINDEX] = rstQueryDef![NUMFOREIGNKEY]
'MsgBox ("Record not found")
prgProgressBar.Value = prgProgressBar.Value + 0.4
Can you not simplify your code with something like:
UPDATE [TBLINPUTFILE] SET [NUMINDEX] = [Hyp].[NUMFOREIGNKEY]
FROM [TBLHYPERIONMANY2] AS [Hyp]
WHERE [Hyp].[COUNTRY] = [TBLINPUTFILE].[COUNTRY]
AND [Hyp].[TYPE] = [TBLINPUTFILE].[TYPE]
AND [Hyp].[BUSINESS_UNIT] = [TBLINPUTFILE].[BUSINESS_UNIT]
AND [Hyp].[L_R_G] = [TBLINPUTFILE].[L_R_G]
AND [Hyp].[REGION] = [TBLINPUTFILE].[REGION]
AND [Hyp].[JOB_FUNCTION] = [TBLINPUTFILE].[JOB_FUNCTION]
I have Vista Business (64bit) and I tried to install MS SQL SERVER 2005. My computer`s name is HFAST. During installation I have choosen 'Windows Authentication" and LocalSystem login. I`m running Administrator account.
Here is my problem: I have installed 2 database engines (one default and one named: SQL2005). Both are installed - I`m sure of it (they both appear when I try to uninstall SQL Serv 2005, and I can see them both on Visual Studio while choosing database to which to connect: http://home.icslab.agh.edu.pl/~martinez/db1.jpg[^] ).
Your single best source of information on this is going to be Organizers[^].
It's probably using either an Access database as a backend or an SQL server or MySql or something else. You'll have to contact them to find out what it's using and how, IF, you can use a different database engine or what it takes to export the data.
I have developed an application in C# and would like to deploy it on a net work.
on one machine the application is working well but other machines on the network cant access it giving this error.
"A connection attempt failed because the connected party did not properly respond after a period of time, or established connection host has failed to respond" .
If have changed the surface area configurations and used the IP address in the connection string .
what could be the problem.
Give the world the best you have, And the best will come your Way
I am trying to install the sql server 2008 july ctp and the setup says I still have some sql server 2000 componets installed.
I am not sure what exact components its having trouble at, Heres the list of my some related components.
Microsoft .Net Framework 1.1
Microsoft .Net Framework 1.1 Hotfix (KB928366)
Microsoft .Net Framework 2.0
Microsoft Codename Astoria
Microsoft Interop Forms Toolkit 1.0
Microsoft SQL Server 2005
Microsoft SQL Server 2005 Analysis Serivces ADOMD.NET
Microsoft SQL Server 2005 Backward Comaptibility
Microsoft SQL Server 2005 Books Online (English) (May 2007)
Microsoft SQL Server 2005 Performance Dashboard Reports
Microsoft SQL Server 2005 Samples
Microsoft SQL Server 2008 Native Client
Microsoft SQL Server Native Client
Microsoft SQL Server Report Pack for Internet Information Services
Microsoft SQL Server Setup Support Files (English)
Microsoft Visual Basic 2005 Express Edition - ENU
Microsoft C# 2005 Express Edition - ENU
Microsoft J# .NET Redistributable Package 1.1
Microsoft Visual Studio .NET 2003 Hotfix (KB927696)
Microsoft Visual Studio .NET Enterprise Architech 2003 - English
Microsoft Visual Studio 2005 Premier Partner Edition - ENU
Microsoft Visual Studio 2005 Toolbox Controls Installer
Microsoft Visual Web Developer 2005 Express Edition - ENU
MSXML 6.0 Parser (KB933579)
PowerGUI 1.0.5 BETA
SQL Server 2000 DTS Designer Components
Please help me with finding all trobling components that has to be removed in order to install either SQL Server 2008 CTP CTP or Client componets of it.
Are you sure your table design is correct? Why do you need to change the table design every time you want to insert data?
What are menu_id1, meanu_id2 and menu_id3? Are they the same type of entity? If so, you should consider a table design composed of tbid and menu_id where the primary key is a composite of the two. Then, if you want to insert data, all you have to do is add new rows of data to the table.
CREATE FUNCTION [dbo].[FLifeId] (@itemid int ,@itemsubid int,@itemssubid int,@typeid int,@subtypeid int)
declare @lifeid int
declare @combinationid int
set @lifeid = 0
set @combinationid = 0
select @combinationid = CombinationId from tblItemsIdComposition where MCPId = @itemid and MCPSubId = MCPSubId and MCPSSubId = @itemssubid
if (@combinationid = 0)
select @lifeid = id1 from a where combid = @combinationid and tyid = @typeid and styid = @subtypeid
This is the ,matter i am typing in my funciton.The thing is , i want to pass two parameters back to the stored procedure , whee i am calling this funciton.
i know i can't return two values back to the stored procs.
Then how should i do it ?
Yahoo Messenger Id = soniagupta1
A function can return either a single scalar value or a table variable. This has been explained to you previously. A stored procedure can return multiple scalar values, so you could consider using one of those instead.
hi guys, is it possible that i can retrieve records from different database like SQL Server 2000 and MySQL?
example, assuming i have table in SQL Server 2000 and i also have table in MySQL. the table in SQL Server 2000 for example is Table1 and in MySQL is Table2.
if i create a query that will combine or retrieve the fields of the two database, is it possible?
if so anyone who have idea with that and how to do that?
Don't block the drive way of all the newbies in programming.