|
It sounds like you have processing half in your database and half in you application. Change the design to move all the processing into one platform or make sure the database returns the results from the update (possibly with a simple select from updated record changing the return type to a datatable)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
So are you saying that the function doesn't see the refreshed situation. If that's the case, perhaps you're running the update in a different transaction or you are feeding 'old' values as parameters to the function.
The function should be able to fetch the current data from the database even if the transaction is still going on as long as you use the same transaction.
|
|
|
|
|
plz take look at edited message...
|
|
|
|
|
Answerred the later post.
|
|
|
|
|
how to select maximum number from table using groupby keyword with two column name
|
|
|
|
|
A little example data would help the answering, but basically you group by like:
SELECT MAX(NumberField),
Field1,
Field2
FROM YourTable
GROUP BY Field1,
Field2
For info and examples, see: GROUP BY (Transact-SQL)[^]
|
|
|
|
|
You have already posted this in the Q&A section.
Try avoiding cross-posts.
The funniest thing about this particular signature is that by the time you realise it doesn't say anything it's too late to stop reading it.
|
|
|
|
|
Hello,
We are in the process of copying over our live database over to a new more robust server.
Is there a script/utility to create all the database objects?
Thanks!
|
|
|
|
|
|
The Database Publishing Wizard[^].
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
If you are just moving the database do backup/restore or detach/attach. These create the tables for you.
|
|
|
|
|
how can i sort data base in c#
ty
|
|
|
|
|
ace300 wrote: how can i sort data base in c#
what do you mean by sorting a database? a database holds tables, in no particular order. And each table holds rows, in no particular order. Getting data out of a database in some sorted way is to be handled by the individual query, not by the database overall.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
I concur with Luc. You don't "sort" a database, you specify that you want the returned data from a query to be "sorted".
General SQL syntax:
Select Field1, Field2
from Table1
Where ...
Order by Field1
The important thing here is the "order by" clause of the select query.
|
|
|
|
|
Do you mean Indexing? Google it!
♫ 99 little bugs in the code,
99 bugs in the code
We fix a bug, compile it again
101 little bugs in the code ♫
|
|
|
|
|
We have a legacy Access DB that is located on a file server. We use Excel VBA to manipulate the database and are having performance issues.
There is a query (stored procedure) in the DB that zeros out data in a large table (~250,000 rows) in preparation for the day's P/L. If I log directly into the DB, I can run the procedure in a second or two. If I execute the procedure in VBA, it takes several minutes and creates a 68 MB temporary file on the local computer.
I tried begin/commit/rollback transactions but that had no effect. I also tried client and server side cursors. Very little difference. I'm guessing it is saving undo information locally. Is there a switch to prevent this?
Is there something I am missing here?
Thanks very much,
Mark Jackson
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=S:\Database\database.mdb;" & _
"Jet OLEDB:System Database=s:\database\security.mdw;" & _
"User ID=xxx;Password=xxx;"
Set conn = New ADODB.Connection
conn.ConnectionString = strCnn
conn.Open
conn.CommandTimeout = 0
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandTimeout = 0
' Set Active values to NULL
cmd.CommandType = adCmdStoredProc
strCmd = "UpdateActiveSetNullValues"
cmd.CommandText = strCmd
cmd.Execute Options:=adExecuteNoRecords
|
|
|
|
|
Most likely this is because Access is just a flat-file and the operations against the data are done by the (local) driver. So there's no server side application to handle this. This causes the data to be transferred over the network etc.
One thing is that you could try to modify the stored procedure. If you're having cursors and loops, try to modify them to work on sets. Another this is that you could create a small program on the server side (for example as a service) which would do the actual operation when called. The operation itself wouldn't be faster but much less network traffic would be included.
And of course if you have the option, you could consider migrating to SQL Server which would include a real server side RDBMS.
|
|
|
|
|
For a variety of reasons migrating isn't an option in the short term although I will do that eventually.
I am curious why I can log into the DB via Access on my local computer with the files on the server, and run the query in two seconds. If I run the exact same query in ADO/VBA, it takes a couple minutes.
I know Access mostly uses the old DAO objects. That may be the difference.
|
|
|
|
|
The difference is that it's not a database-server. It's not meant to be accessed from a remote computer, and even though it's possible, it'll never be fast.
Why is migrating not an option? You could migrate the tables and leave your logic, code, forms and data in the MS-Access database. The database could fetch it's records from Sql Server, without the embedded code ever noticing a difference.
--edit
There's a list of possible bottlenecks over here[^], could be usefull.
I are Troll 
|
|
|
|
|
I will migrate next month but was looking for a fix for the rest of this month.
|
|
|
|
|
One main reason is, as you said, the difference in the technology stack. Access has some more capabilities to handle the database file than if you connect to the database from Excel/VBA.
Creating a small application using .Net framework could result into very different response times, so that could also be an option. But as I wrote, if you want to minimize the network traffic, the best option before migrating could be to use a small server side application.
|
|
|
|
|
As Mika suggested, it'd be a good time to move to a real database-server. It'd perform a lot better, and there's a Wizard in Access that can migrate all your data (if Sql Server is installed). There are "free" versions of Sql Server available, so the cost shouldn't be a problem.
I are Troll
|
|
|
|
|
If you migrate, watch for column type conversions that do not make sense. i.e. Postal code converted to FLOAT. Just because you have an MS Access column as "text" does not mean Microsoft will keep it. Microsoft knows better, you really wanted it as "float". 
|
|
|
|
|
How 'bout this ...
Can you create an EXE (VB, C#) that you can place on the server which will perform the "zero out" function. You could then schedule that program to run every morning.
The folks here could help you with connecting to a MS-Access database and execute SQL statements.
I will help you if you want the solution in VB.NET.
Regards,
David
|
|
|
|
|
I don't understand the problem.
Obviously it is a manual process. But one that in the worst case only takes a couple of minutes.
And you said you plan to move to a different database in a month.
So the obvious solution for this month is either to do it on your local box and find something else to do for a couple of minutes or log into the file server.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
