binky
Last Activity: Mar 28 '08, 06:26 PM
Joined: Nov 2 '07
Location:
-
Well I was able to whittle it down to about 36,000 records to review, and it seems to be working. Thanks for your help :) -
The records I have to sort through in t_trans total over 15 million. I need to sorth through ALL those records to find the trans_code 10 and 30's, and those alone total over 6 million.
I'm hesitant to use a temp table for that since I'm not sure how much resources it will use.Leave a comment:
-
Complex query (compare records in same table / join a 2nd table)
Question for all you SQL gurus out there. I have a (seemingly) complex query to write, and since
I'm just learning SQL server, am not sure how to go about it.
Tables:
t_trans
t_account
All tables are bound by an SSN column ("SSN").
t_trans has the bulk of the data I need, and includes the following data:
ssn, trans_date, trans_amount, trans_code
111-11-1111,... -
I contimplated having a toggle button that will turn the box on and off, but the users really want it on all the time.
I tried:
The above works, but actually seems to run slower than the following:Code:lstSearch.RowSource = "EXEC dbo.usp_ssn_exception_srch '" & Combo102 & "', '" & Combo143 & "', '" & key_field1 & "'"
...Code:lstSearch.RowSource
Leave a comment:
-
I tried two different methods: using a stored procedure and building the SQL source on the fly.
Building it dynamically:
The stored procedure...Code:lstSearch.RowSource = "SELECT key_field2 AS k2, key_field3 AS k3, key_field4 as k4, key_field5 as k5, err_field1 as e1, err_field2 as e2 FROM cv WHERE cv.key_field1 = '" & key_field1 & "'" lstSearch.Requery
Leave a comment:
-
The table i'm pulling from has about 20 columns, and i'm grabbing six of them. All the fields in my select and where clauses happen to be indexed, so that shouldn't be an issue.
I think it's less of a server problem and more of a client problem, i.e. the listbox.requery command that's causing it.Leave a comment:
-
ListBox requery is too slow
Good afternoon folks, I have a performance question if anybody might have suggestions.
Functionally, everything i'm about to describe works as intended. The only problem I'm having is speed.
Assume:
1) Using MS Access 2003 in an ADP project, SQL Server 2000 Enterprise backend
2) Main form is populated by a stored procedure, which works flawlessly
3) There is a listbox on the form that... -
Auto correcting missing data
Good evening folks, this forum has been very helpful, but I have one last hurdle I am trying to overcome.
I have two tables with a one to one relationship (T1 and T2). Example data looks like:
...Code:T1: Field1 Field2 (PK1 to T2) ---------------------------------- 1111111 1 1111111 2 1111111 3 1111111 4 2222222
-
Not good enough unfortunately, it's still not working consistently :/Leave a comment:
-
Correct, this record set is for viewing and updates only.
No deletes or additions on this form.Leave a comment:
-
Take it back, I thought I fixed it.
Suddenly not working again :(Leave a comment:
-
I fixed it!
I added the following to the Form's BeforeInsert property:
Now the form loads with blank controls that can't be edited, then can be edited once a valid recordset is loaded.Code:Private Sub Form_BeforeInsert(Cancel As Integer) Cancel = True End SubLeave a comment:
-
AllowAdditions is set to NO currently.
I do not want to allow additions to the recordset that i'm displaying though, which is why it's set to NO.Leave a comment:
-
No Records returned hides form controls
Quick question that I can't find a solution to:
I have an ADP application that uses SQL Server 2000 backend, along with stored procedures that populate my forms. I do not want to use subforms for various reasons, so my data is loaded into a single form (record source is a stored procedure, and the form's InputParameters are changed depending on the data to load, pretty standard).
However, if no records are returned... -
Finding and Correcting Missing Column data in a single table
Good afternoon folks, a quick question for all you SQL gurus out there. I'm cleaning up a table from another person at my company and am running into a small problem.
Assume:
Table: T1
-----------------
Field1 - String, not unique
Field2 - String, not unique
No PK is defined.
I have 58,000 some odd entries in T1, there are other columns but I'm only concerned with... -
Thanks for the reply :)
I might have found a workaround, and that's by migrating everything from a MDB project into an ADP project. Then I can use my existing procedure as the recordsource on a form and Access apparently handles the edits with all the appropriate record locks automatically.
But I will also try your suggestions, and see what works best :)Leave a comment:
-
/bump for anybody that might be able to help :)Leave a comment:
-
The stored procedure is below. It's a simple SQL query that uses parameters passed from the application.
...Code:IF OBJECT_ID('my_procedure') IS NOT NULL DROP PROCEDURE my_procedure GO CREATE PROCEDURE dbo.my_procedure (@p_cPrcsNm CHAR (15) ,@p_cErrMsg CHAR (75) ,@p_vButton int ,@p_vAssgnCd VARCHAR (3) ,@p_vRouteCd VARCHARLeave a comment:
-
Updating Data in a Recordset (Stored Procedure)
Hi folks, I'm just learning how to use MS Access as a front end for SQL Server, and have a question:
I have a stored procedure that returns a set of records from a SQL Server and loads it into a form in my Access application. This works as intended, but I'm having trouble trying to figure out how to modify records on the form... whenever I try i get the error that says youc an't modify this data because its based on an expression....
No activity results to display
Leave a comment: