|
I would like to be able to assign the value from a created QueryDef object to a modifiable recordset object variable. How do I go about doing this? The Microsoft documentation does not provide any explanations.
Here is the code so far:
Private Sub ProcessHeadcountRecords()
Dim dbsHeadcount As Database
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 qdfNew As QueryDef
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 & ""
Cnxn.Open strConn
Set rstInputFile = New ADODB.Recordset
strSQLInputFile = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _
"[L_R_G], [REGION], [JOB_FUNCTION], [ACTUAL], [NUMINDEX] " & _
"FROM TBLINPUTFILE"
rstInputFile.Open strSQLInputFile, Cnxn, adOpenKeyset, adLockOptimistic
rstInputFile.MoveFirst
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"
Do Until rstInputFile.EOF
With dbsHeadcount
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]) & "'"
rstHyperionMany.Requery
Set qdfNew = dbsHeadcount.CreateQueryDef(strFileName, strSQLHyperionMany)
DoCmd.SetWarnings False
'If rstInputFile.RecordCount = 1 Then
rstInputFile![NUMINDEX] = QueryDefs![strFileName]![NUMFOREIGNKEY] /// I DO NOT KNOW THE SYNTAX HERE TO ADDRESS THE QUERYDEF OBJECT!!! PLEASE HELP!!!
'Else
'MsgBox ("Record not found")
'End If
'prgProgressBar.Value = prgProgressBar.Value + 1
End With
dbsHeadcount.QueryDefs.Delete strFileName
rstInputFile.MoveNext
Loop
End Sub
-- modified at 13:21 Friday 5th October, 2007
|
|
|
|
|
I am using a CrossJion for Present the values for a few selected columns base on there [Post Date] Dimension, I also need to present the difference between these columns from [Post Date] to [Post Date].Lag(1): Any suggestions??
Code
SELECT DISTINCT({CrossJoin({[Post Date].[^@BINAV_Param_Date_priormonth@^] , [Post Date].[^@BINAV_Param_Date_priormonth@^].Lag(1)},{[Measures].[Current Balance],[Measures].[Weighted AVG Interest Rate],[Measures].[Average Balance]})
}) ON COLUMNS,
NON EMPTY DISTINCT ({[Product].[Time Deposit]}) ON ROWS
Thank You,
Don
|
|
|
|
|
Hi,
I got this off the .NET Tiers documentation:
Description is provided for Tables, Columns, and Keys..
Is it possible to create a description for the columns, and keys, etc? For what else can one create descriptions for?
Here is a sample description for a table:
-- Add Table Description
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'All allowable Checking Account types for my ABC System' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'BankAccountType'
Please can someone advise?
Regards
|
|
|
|
|
|
HI All,
Is there any way to increase the excecution time of my stored procedures in a SQL server database.
I have a couple of stored procedures that takes time to run.
Thanks
|
|
|
|
|
Optimization in SQL is an art where experience is the best teacher. To begin with, seperate any queries you are making and run them through the profiler and see if there is any obvious changes. Adding the appropriate indexes or a more restrictive filter (WHERE Clause) can help tremendously.
By seperating them, you will also find your bottle neck code. You have to experiment with different techniques to see what will execute faster. Sometimes breaking up a large slow query into smaller queries is much faster (sometimes it works the other way). It really depends upon your data. There is no magic button.
|
|
|
|
|
Thank you ver much,
I am not really a database person,Can you please tell me how i should index my database?
Thank you very much for your time.
|
|
|
|
|
Indexing is a table by table, query by query decision. I have no idea about the structure of your data or your queries.
Take a look at your slow query's WHERE clause. This may dictate the index for you. Using an example query:
SELECT
*
FROM
mytable
WHERE
myDate < '1/1/2007' and
myColor = 'Green'
Here you would examine your data and take a good guess as to which column filter reduces the result set the most. Lets say it is [myColor]:
CREATE INDEX myIndex ON myTable
(
mycolor,
mydate
)
|
|
|
|
|
Thank you very much.
Do i have to do them on all stored procedures?
Thanks very much.
|
|
|
|
|
No, only the SPs that are running slow or those that are accessing tables that will become very large in time.
The whole idea is to limit the amount of IO necessary for SQL Server to access the data. Everytime SQL has to hit the disks it costs time. Indexes let SQL find the appropriate rows quickly with limited IO.
Putting indexes on small tables does not improve speed access speed since the whole table can be read in 1 or 2 IO's anyway.
|
|
|
|
|
Indexing a SQL database is quite a topic in itself.
Read up on it - google it and you will find lots of useful information.
One aspect with SQL indexes that is important to understand is whether, or not, to make the indexes clustered indexes.
Creating clustered indexes can massively speed up data access and it can also create a massive slowing down of data access.
Without going into too much detail, and without being 100% accurate, clustering a table means that the last node on the index is the data and not a pointer(as is the case in non-clustered indexes).
This means that if you have a table that has very few updates on it then there is a good chance that a clustered index is what you want.
A table with a lot of updates is generally not a candidate for clustering; as when the index is written a lot of data may have to be moved around on the disk.
Also you can only have 1 clustered index per table.
The above is just to give you a bit of an idea about what indexing in SQL entails.
Understanding indexes is so important for optimising data access that you will be doing yourself a real favour to read up on it.
I hope this helps...
You always pass failure on the way to success.
|
|
|
|
|
hi there,
i'm looking for a free tool to to generate random testdata for sql server. do you have any ideas, experiences, links?
thanks in advance!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by.
[Douglas Adams]
|
|
|
|
|
HI
WE HAVE TO STORE IMAGES IN A SQLDATA BASE .
THE IMAGES ARE IN A PATICULAR SPECIFIED FOLDER(I.E. IN F - folder ).
NOW WE HAVE TO PASS THAT IMAGES FROM THAT FOLDER TO DATA BASE.
IN THIS ISSUE WE R FACING THE PROBLEM.
CAN U UR SUGGESSTIONS AND RELATED WEBSITES.
REGARDS
KISHORE
|
|
|
|
|
1 - don't shout
2 - try google, this has been covered hundreds of times, at least.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
|
In case you don't know, all capitals is considered to be shouting. Don't be rude like that, and no textspeak.
"Find it your bloody self - immediately!" - Dave Kreskowiak
|
|
|
|
|
Hi,
I have created a DataSet(dsLocal) containing rows from local database that I wish to update with the rows taken from another dataset (dsWeb).
dsWeb dataset may contain Newly added rows and/or modified rows.
dsweb is populated from the Web server Database and dsLocal is populated from Local database
Both Local and Web server database schema are same.
What dataset methods/properties should i use?
|
|
|
|
|
You will need to loop through the tables in the webservice dataset. For each table you will need to loop through each row. Then you need to check if the row already exists in the correlating local dataset/table. If so, update the row, if not add a new one. I've never found a very nice way to move a row from one table to another. The only way that works for me is to just loop through the columns and move the data column by column. Sort of like this:
For Each col As DataColumn In dtOld.Columns<br />
If dtNew.Columns.Contains(col.ColumnName) Then<br />
rowNew(col.ColumnName) = row(col.ColumnName)<br />
End If<br />
Next
Hope this helps get you started.
|
|
|
|
|
Thanks for your reply.
I will work out the solution provided by u.
And I will let u know if any queries are there.
|
|
|
|
|
Thanks,
Will you please tell me what is this rowNew and row
rowNew(col.ColumnName) = row(col.ColumnName)
Waiting for your reply
|
|
|
|
|
Oops. Sorry. I should have clarified that. The rowNew is a DataRow object. You will want to set it equal to the new datatable's NewRow in order to give it the schema. Like this:
Dim rowNew as DataRow = dtNew.NewRow
The row is also a DataRow object, but it is from the old table. You will want to loop through each row, and then for each row loop through the columns like I showed you before. Like this:
For Each row as DataRow in dtNew.Rows<br />
<br />
'The column loop goes here<br />
<br />
Next
Hope this helps.
|
|
|
|
|
Hi,
I wrote this update query. But it doesn't work.
The error message : An aggregate may not appear in the set list of an UPDATE statement.
Here is my query.
update udtStudent set score=sum(b.studentscore)
from udtStudent a inner join udtQuestionHistory b on a.testid=b.testid and a.empno=b.empno
where a.score is null
group by b.empno,b.testid
i wish to update the score = sum of studentscore from udtQuestionHistory
the problem is at sum().Can anyone advice me how should i overcome it?
Thanks in advance.
thanks.
|
|
|
|
|
Eunice (VB junior) wrote: set score=sum(b.studentscore)
from udtStudent a inner join udtQuestionHistory b on a.testid=b.testid and a.empno=b.empno
where a.score is null
group by b.empno,b.testid
Try to bring the sum in a select clause and assign it to an integer variable.
For instance,
Declare @intSum INT
select @intSum = sum(score) from student a inner join history b on a.testid = b.testid
Now, @intSum will hold the sum of studentscore from history table.
Later, proceed with your update statement like
update student set score = @intSum where ##your condition##
Hope that thought might help you.
|
|
|
|
|
Hi CS,
thanks for the guidance. I got what you meant.
Here is what i did according to your advice and it works.
Declare @intSum INT
declare @testID int
declare @empno int
select @intSum = sum(studentscore),@testid=testid,@empno=empno from udtquestionhistory
group by empno,testid,questype,testtype,partnumber,process
update udtstudent set score=@intSum where testid=@testid and empno=@empno
Thanks for the help.
|
|
|
|
|
You've already asked this question once. Please don't double-post.
Paul Marfleet
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
