Database

Simultaneous access to MS Access

27-Jan-09 20:33

Don't know what kind of sample you're after. Do you have a small package and a query you could post. We could have a look how it could be converted to SQL Server.

It seems that there are also tools that help in the conversion: http://www.google.com/search?hl=en&q=oracle+package+convert+sql+server+procedure&meta=[^]

The need to optimize rises from a bad design.My articles[^]

Member 382292227-Jan-09 12:20

27-Jan-09 12:20

I've been asked to write a program for a small company. They will have just a few users (1 to 4) accessing a database. The database will probably grow to just 20,000 records at the most. If multiple users are accessing simultaneously and one of them adds or updates a record, they want the screens of the other users to be refreshed and reflect the newly added data.

My thought is to write the application in MSVC++ and connect to an MS Access DB using ODBC. My dilemma is in deciding the best way to handle the updating of all the users screens when one user has added a record.

1) I could have the application spawn a thread, which would kickoff a timer. Say every 20 seconds, the timer would run a query against the DB and see if any records have been added. If so, it's time to refresh the data here on this machine.

2) I could also maybe write a service that would run on the server with the database. The application on each users machine would interact with this service, not the database. The service would accept all the requests from the users (clients), interact with the DB and respond to the clients. If a record is added or updated, the service would send a msg or invoke a callback on the client and tell it to update.

3) I'm not sure about this, but I think there also might be some broadcast function/message that I can use.

Does anyone have any suggestions or warnings or previous experience with this?

Thanks very much!
Brian

Wendelius27-Jan-09 12:46

27-Jan-09 12:46

Few thoughts:

Member 3822922 wrote:
I could have the application spawn a thread, which would kickoff a timer

This should work fine. I've used similar technique earlier with no problems.

Member 3822922 wrote:
I could also maybe write a service that would run on the server with the database

This is a slightly modifed version from the previous and in my opinion it would add components and logic to your application. Unless the refreshing is time critical, I doubt if this version is worth the effort.

Member 3822922 wrote:
I think there also might be some broadcast function/message that I can use

This is beyond my knowledge, but I have a feeling that this also could cause (much) work, so is it worth it.

Don't know if you've made the final decision for the products to use, but have you considered using SQL Server. I admit that it would cause a little more work when building the environment, but also it has some benefits compared to Access, like:
- true database engine so you would have much more options in implementation
- better backup mechanisms (online backups, log backups if needed etc)
- much better performance
- far better concurrency etc.

What comes to the options, for example one thing is that .Net Framework has SqlDependency[^] class which can be used to receive notification when the data changes. This would eliminate the need for timer based polling.

Since I don't know anymore about the program I'm not able to give more examples but I'm quite confident that productivity would be greater when using SQL Server since .Net Framework has large amount of specialized classes for use with SQL Server. As far as I can see there's nothing similar against Access so you would have to use basic ODBC, as you mentioned.

The need to optimize rises from a bad design.My articles[^]

Member 38229225-Feb-09 13:48

5-Feb-09 13:48

I just wanted to thank you for responding (back on 1/27) to my question at Code Project. The company decided not to proceed.

Anyway, thank you -
Brian

PIEBALDconsult27-Jan-09 15:13

PIEBALDconsult

27-Jan-09 15:13

Member 3822922 wrote:
connect to an MS Access DB using ODBC

Not OleDb?

Member 3822922 wrote:
they want the screens of the other users to be refreshed and reflect the newly added data

Sounds awful. Why would this be required? I prefer not to keep a bunch of data on the clients.

I would likely go with 1. You could also have the insert/update/delete methods update a table of timestamps, so when a client looks for changes it can query the timestamps to know which (if any) data has changed.

Member 38229225-Feb-09 13:50

5-Feb-09 13:50

I just wanted to thank you for responding (back on 1/27) to my question at Code Project. The company decided not to proceed.

Thanks again,
Brian

PIEBALDconsult5-Feb-09 14:11

PIEBALDconsult

5-Feb-09 14:11

I hope that's good news.

Member 38229225-Feb-09 14:32

Getting values from two tables.

5-Feb-09 14:32

I'm certainly ok with their decision. My primary goal is that the customer be happy and make the best decision for themselves. But I also think it would have been a fun project.

kurangu27-Jan-09 6:40

kurangu

27-Jan-09 6:40

Hi,

I have two tables like below.

select childname,wgt from
#Hy order by wgt desc

select childname,ror from
#Hy1 order by ror desc

I need like below.

#Hy.childname #HY.wgt #Hy1.childname #hy1.ror

Just like appending the two tables without altering the result set
means first one should be wgt desc and ror should be desc.

Re: Getting values from two tables.

Wendelius27-Jan-09 6:58

Re: Getting values from two tables.

27-Jan-09 6:58

Do you mean that if the first query returns 10 rows and the second 20 rows, you will have only one result set, 30 rows?

If that's the case you could use union and if you want the sorting to remain so that rows from first result set are first and then from the second one, you could have something like:

select 1, childname,wgt 
from #Hy 
union
select 2, childname,ror 
from #Hy1 
order by 1, 2 desc

The need to optimize rises from a bad design.My articles[^]

kurangu27-Jan-09 7:33

kurangu

27-Jan-09 7:33

HI,

It will just combine the values.I need in columns like

#hy1.childname #hy1.ror #hy.childname #hy.wgt without altering the sorting.

Re: Getting values from two tables.

Wendelius27-Jan-09 7:59

Multiple Values In single Parameter in oracle

27-Jan-09 7:59

AFAIK that won't be possible using a single query and it will be problematic if the result sets have different amount of rows. For example, if the first result set is 10 rows and the second one is 20 row, the only way that comes in mind is something like:
- insert the first sorted result set (10 rows) to a temporary table
- add two columns to the temporary table
- update the existing 10 rows with the first 10 rows of the second sorted result set
- insert the rest 10 rows from the second sorted result set to the table in the columns 3 and 4.

However this will lead to a situation where you have 20 rows but the column 1 and 2 for the last 10 rows will be null. It makes me wonder what is the use for this kind of result.

The need to optimize rises from a bad design.My articles[^]

vikram_asv27-Jan-09 5:42

vikram_asv

27-Jan-09 5:42

Hello Experts,
I Create One store proc in oracle 10g . I am getting values but its is taking only single values not taking mulitiple values when i am calling this store procedure from my c# application. But i need mulitiple values in single parametre . Below is my store procedure What i am getting is when i am calling this store proc from C# its taking like this '035,034,023,022,021,015,013,011,010,009,008,007' thats why its not getting values but what i need is i dont want like i want lie this 035,034,023,022,021,015,013,011,010,009,008,007 (or) '035','034','023','022','021','015','013','011','010','009','008','007' . Then It will work For me . All i need is like this I tried it somany ways but no luck If any one can help us then thankful to him.

CREATE OR REPLACE PROCEDURE SC_SP_SelectTranscribe(
in_appname in varchar2 ,
in_subappname in varchar2,
in_taskname in varchar2,
in_functionname in varchar2,
in_variablename in varchar2,
in_valuename in varchar2,
in_variablename2 in varchar2,
in_valuename2 in varchar2,
p_cursor OUT SYS_REFCURSOR)
is
ln_end number;
lv_smv varchar2(100);
lv_temp varchar2(100);
sql_str varchar2(8000);
begin
ln_end :=0;
lv_smv := in_valuename;
ln_end := instr(lv_smv,',',1);
if ln_end = 0 then
-- This condition becomes true when there is
lv_temp := lv_smv;
else
--lv_temp := replace(lv_smv,'%',chr(39));
lv_temp := substr(lv_smv,1,length(lv_smv)-1);
end if;
open p_cursor FOR
SELECT distinct RecordID
FROM sc_tbl_apprepository
WHERE(
(sc_tbl_apprepository.taskname=in_taskname)
AND
(sc_tbl_apprepository.FUNCTIONNAME=in_functionname)
AND
sc_tbl_apprepository.RecordID in (SELECT sc_tbl_apprepository.RecordID
FROM sc_tbl_apprepository
WHERE (sc_tbl_apprepository.taskname=in_taskname) AND (sc_tbl_apprepository.FUNCTIONNAME=in_functionname) AND
(sc_tbl_apprepository.fieldname =in_variablename) AND
--'035,034,023,022,021,015,013,011,010,009,008,007' (sc_tbl_apprepository.fieldvalue in(substr(lv_smv,1,length(lv_smv)-1))))) AND
sc_tbl_apprepository.RecordID in (SELECT sc_tbl_apprepository.RecordID
FROM sc_tbl_apprepository WHERE (sc_tbl_apprepository.taskname=in_taskname) AND (sc_tbl_apprepository.FUNCTIONNAME=in_functionname) AND
(sc_tbl_apprepository.fieldname=in_variablename2) AND
(sc_tbl_apprepository.fieldvalue =in_valuename2));
end;

Re: Multiple Values In single Parameter in oracle

Wendelius27-Jan-09 6:07

SQL Server 2005 and SQL Server 2008 on the same server

27-Jan-09 6:07

Which client are you using? The Oracle provider that ships with .Net Framework or Oracle's own ODP.NET?

The need to optimize rises from a bad design.My articles[^]

pzn3xq27-Jan-09 4:10

pzn3xq

27-Jan-09 4:10

Hi
We are planning on installing SQL Server 2008 on the same server that already has SQL Server 2005. The server is Server 2008. Any problems with loading both db's on the same server? We will use a completely different instance name for SQL Server 2008 than SQL Server 2005.

Anyone encounter any problems with this?

Thanks

Re: SQL Server 2005 and SQL Server 2008 on the same server

Wendelius27-Jan-09 4:46

Slow query when using @variable in Where clause [modified]

27-Jan-09 4:46

I'm running both without any problems. Just make sure to use different ports and remember that only one can be default instance.

The need to optimize rises from a bad design.My articles[^]

Meysam Mahfouzi27-Jan-09 4:06

Re: Slow query when using @variable in Where clause

27-Jan-09 4:06

I've got a table containing around 1,500,000 rows. This table has a column called "Culture" whose value could be either 0 or 1. Right now, all the values are 1.

The following query executes very fast (less than a second):

SELECT TOP 10 * FROM News
WHERE Culture = 1

The following query executes very fast as well:

SELECT TOP 10 * FROM News
WHERE Culture = 0

When I put a variable in where clause, it still executes very fast (less than a sec):

DECLARE @c TINYINT
SET @c = 1

SELECT TOP 10 * FROM News
WHERE Culture = @c

But the following query takes several minutes to complete:

DECLARE @c TINYINT
SET @c = 0

SELECT TOP 10 * FROM News
WHERE Culture = @c

There two things to mention:

1- There is a nonclustered index which contains Culture field.
2- The same thing happens if I execute the four above-mentioned queries wrapped in a stored procedure.

I'm really confused D'Oh! | :doh:

I don't know what the problem is...

Rob Philpott27-Jan-09 4:25

Rob Philpott

27-Jan-09 4:25

I had a very similar issue recently myself. Not eaxactly sure why it is but it was suggested that SQL Server can't optimise queries which contain variables - they could change as the query executes.

Anyway, the general conclusion and advice I drew was to avoid using variables and join onto other tables where possible. Have you tried inserting @c into a single line temporary table and joining on that? I admit that its rather a grubby solution, but then again it is SQL Server and sometimes you have to hold its hand.

Regards,
Rob Philpott.

Re: Slow query when using @variable in Where clause

Meysam Mahfouzi27-Jan-09 8:18

Re: Slow query when using @variable in Where clause [modified]

27-Jan-09 8:18

Hi Rob,
Thanks for suggested solution. I'll test it as soon as I get access to database tomorrow morning.

Meysam Mahfouzi27-Jan-09 20:45

Re: Slow query when using @variable in Where clause

27-Jan-09 20:45

Rob Philpott wrote:
Have you tried inserting @c into a single line temporary table and joining on that?

Dear Rob,

I used the following query but it didn't make any difference:

DECLARE @Start INT, @Count INT

SET @Start = 10
SET @Count = 5


DECLARE @c TINYINT
SET @c = 0;

WITH paging AS (
	SELECT id, title, [description], ROW_NUMBER() OVER (ORDER BY Date DESC) rownum
	FROM news
		JOIN (SELECT @C AS Cult) AS CC ON news.Culture = CC.Cult
	--WHERE Culture = @c
)
SELECT * FROM paging WHERE rownum BETWEEN @Start AND (@Start + @Count - 1)

modified on Wednesday, January 28, 2009 3:03 AM

Rob Philpott27-Jan-09 23:49

Rob Philpott

27-Jan-09 23:49

Right, might be barking up the wrong tree then. How about using a proper temporary table such:

declare @c tinyint
set @c = 0 	
 	
create table #c (Culture tinyint)

insert into #c select @c

select id, title, [description] from news join #c on news.Culture = #c.Culture

drop table #c

Regards,
Rob Philpott.

Re: Slow query when using @variable in Where clause

Ben Fair27-Jan-09 4:43

Ben Fair

27-Jan-09 4:43

Have you compared the execution plans? That should shed some light on how SQL Server is processing them differently and what its doing that is making it take longer.

Hope in one hand and poop in the other; see which fills up first. Hope and change were good slogans, now show us more than words.

Re: Slow query when using @variable in Where clause

Meysam Mahfouzi27-Jan-09 8:21