If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Msde ot Jet?
Hi ,
I have been trying to get my head around a database issue for an application that I am planning. The biggest problem I am faced with is a lack of clear information and knowledge of certain issues. The scenario is this: I have a primary accounting application that runs msde, certain tables in msde holds vital data that I need to access for the application that I am planning. I can simply open an ODBC connection to msde and link tables via ODBC. My concern is that this method is sluggish and performance will be hampered when I distribute the application over the network. There will be about 7 heavy uses of the app that I am planning and about 5 light users. I expect about 5 concurrent sessions at any time. The data that will be provided by msde will not be amended in any way for the most part, but it will be contorted in various ways through queries etc so that the data is presented logically and pragmatically to my end users in my app. Is using a jet database the best approach here? I am familiar with jet and I especially like the tight integration of jet with access. I would prefer to use this approach, provided that it does not leave me with users tapping on the desk and giving me glares because of the lack of performance. Is it possible to connect to msde using ADO , and then writing to a jet back end? Will this be faster than simply linking tables via ODBC? Is it possible to have a mixed approach like this? Any help here would be greatly appreciated. Please feel free to knock me over the head if I have not made myself clear, I will try and answer any queries. Regards Andy |
#2
|
|||
|
|||
"=?Utf-8?B?QW5keQ==?=" wrote in
: A few answers to pick up from a non-expert... I can simply open an ODBC connection to msde and link tables via ODBC. Or, even easier, use an ADP, which is specifically designed to talk to MSDE and SQLS. .. My concern is that this method is sluggish and performance will be hampered when I distribute the application over the network. Don't see why: if you take care to minimise network traffic, all the work is done on the server. There will be about 7 heavy uses of the app that I am planning and about 5 light users. I expect about 5 concurrent sessions at any time. This is a lot for MSDE -- my version (1.0) starts to penalise access heavily after 4 connections. Bearing in mind that a session does not need to have a Connection object in use all the time though, you may be okay. I think that v2 counts concurrency in a different way though. The data that will be provided by msde will not be amended in any way for the most part, but it will be contorted in various ways through queries etc Is there an opportunity for local apps just to download snapshots into local tables and work on them from there? Over a WAN, that may be thing to do. Alternatively, what about MSQuery or Crystal reports or something designed to do flexible reporting? Is it possible to connect to msde using ADO , and then writing to a jet back end? Will this be faster than simply linking tables via ODBC? Is it possible to have a mixed approach like this? If you are making small queries on large databases, I would probably just use an ADP; if you are repeatedly hitting nearly the whole tables, then downloading the whole lot once would start to look kinder to the network. HTH Tim F |
#3
|
|||
|
|||
Thank you Tim for taking the time to respond:
I have thought of going the ADP route but what concrens me is the built in degradation on msde ,This is why I think jet would be preferable , and maybe pulling data from msde using ADO. The other solution I considered is using sql 2005 express. But I am not sure how express will work with access , perhaps you know a little more about express? "Tim Ferguson" wrote: "=?Utf-8?B?QW5keQ==?=" wrote in : A few answers to pick up from a non-expert... I can simply open an ODBC connection to msde and link tables via ODBC. Or, even easier, use an ADP, which is specifically designed to talk to MSDE and SQLS. .. My concern is that this method is sluggish and performance will be hampered when I distribute the application over the network. Don't see why: if you take care to minimise network traffic, all the work is done on the server. There will be about 7 heavy uses of the app that I am planning and about 5 light users. I expect about 5 concurrent sessions at any time. This is a lot for MSDE -- my version (1.0) starts to penalise access heavily after 4 connections. Bearing in mind that a session does not need to have a Connection object in use all the time though, you may be okay. I think that v2 counts concurrency in a different way though. The data that will be provided by msde will not be amended in any way for the most part, but it will be contorted in various ways through queries etc Is there an opportunity for local apps just to download snapshots into local tables and work on them from there? Over a WAN, that may be thing to do. Alternatively, what about MSQuery or Crystal reports or something designed to do flexible reporting? Is it possible to connect to msde using ADO , and then writing to a jet back end? Will this be faster than simply linking tables via ODBC? Is it possible to have a mixed approach like this? If you are making small queries on large databases, I would probably just use an ADP; if you are repeatedly hitting nearly the whole tables, then downloading the whole lot once would start to look kinder to the network. HTH Tim F |
#4
|
|||
|
|||
"=?Utf-8?B?YU5EWQ==?=" wrote in
: I have thought of going the ADP route but what concrens me is the built in degradation on msde , The only way round this is SQL Server itself; if the cash for that is difficult, then MySQL is certainly feasible. This is why I think jet would be preferable Well, yes: with care jet does work well with multiple users, but there are problems, fatal file corruption being one of them. It is _very_ sensitive to small network problems. And a WAN would rule out a shared-Jet solution. If you really are just carrying out decision-support types of analysis, without any updates, then probably you could manage with just downloading the dataset once into local (i.e. not even shared) jet databases for further manipulation. The other solution I considered is using sql 2005 express. But I am not sure how express will work with access , perhaps you know a little more about express? Not really: there is a lot of information on the MS site about licensing the different flavours of SQL Server. I did read it although much of it did not make a lot of sense to me. It would be worth while looking at the options there. All the best Tim F |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Power User and MSDE do not get along! | Charles E Finkenbiner | Setup, Installing & Configuration | 0 | January 3rd, 2005 11:29 PM |
Need help: Can't create ADP Database in MSDE from MS Access | Lou Arnold | General Discussion | 0 | July 21st, 2004 04:54 AM |
Conversion to MSDE | birdman | General Discussion | 17 | July 8th, 2004 11:41 PM |
MSDE | Brent Wherry | General Discussion | 4 | June 13th, 2004 09:52 PM |