Is sqlce better than access DB or an excel sheet?

[sous2817]

Hello everyone,

I've got a series of static lists that I need to reference in my program. Being new to all of this, I'm not sure if one method of housing data is better than another. While I have access to an Oracle DB, I thought that having the lists stored locally would be better performance wise. I don't have any issues (yet) regarding getting data from either a SQLCE database, Access 2007 database, or an Excel 2007 sheet. My question is, is there a benefit of using one over the other? Is performance better using SQLCE over one of the other methods? The lists are plain text, nothing too fancy. Perhaps, depending on how involved things get, perhaps a few images (which would take the Excel sheet out of the running).

If anyone has any links or advice on SQLCE compared to Access, I'd appreciate it.

Thanks!

 

[jmcilhinney]

If there's a small amount of data and it won;t change then you should store it inside the application as resources.

 

[sous2817]

Thanks for the help jmcilhinney! If the application calls for subsetting the lists (say country by region) or allowing the user to select a few indications from a list and then populate a datagrid with relevant information, is the http://www.vbdotnetforums.com/members/jmcilhinney.htmlresource approach still appropriate? How I'm currently doing the subsetting is with a SQL query. I've never played with the application resources before, so any advice you can give is appreciated.

 

[jmcilhinney]

If you need to query the data in various ways then a database can be a good choice, although you can still do that in code using resources if the amount of data is small and won't change. You should probably provide all the relevant data if you want the best advice.

 

[sous2817]

I've got 13 tables (actually they're Excel sheets that I'm using ADO to pull from) now that range from a few hundred rows to the largest being about 5000 rows. The user is able to select up to five options from a combobox and I'm using a SQL query to retrieve the relevant rows from one of the sheets. These 13 tables will be updated monthly and remain static through the month.

I also have a few other static lists that I have in another excel sheet that I use to populate a second combobox. Basically I have a dropdown with 6 regions, and when the user selects a region, the combobox populates with the countries that are in that region.

The ADO solution works, but I was wondering if porting things over to an Access or SQLCE database would be more efficient...or maybe more professional looking? Granted I'm not dealing with massive amounts of data, so any efficiencies would probably be pretty negligible, so maybe I'm looking for a "best practice" answer. I have been told "well if it works, then why bother", but that's not really the answer I was after.

Thanks again for your time!

 

[jmcilhinney]

That is definitely not a situation to use resources. The Excel option might be easier from the point of view of updating the data, because Excel is more human-readable and is often an output format for data extraction tools. In use, I'd think that a CE database would be the best option, followed by Access and then Excel. If you're using a database though, you'd have to build an import routine to get the new data in each month.

 

[sous2817]

Thanks again for the advice! I'll go the SQLCE route, it'll also give me a chance to bolster my SQL a bit.

As far as the import routine, I'll have to take a stab at that as well. From what I've researched SQLCE doesn't have a bulk import so it seems it'll have to be a line by line update. That won't be so bad if I can tie it in to some sort of monthly maintenance or something like that. The only other option I found, and by far the most round-about, was to import the Excel sheets in to Access, and then use a program I found that converts an access DB to a sqlce DB.

Either way, you've once again helped me with my issue and I appreciate you taking the time.