Help with code or general advice pls

[robbie73]

Hello, i was hoping some body could help me with some code
and general advice.

I am starting a project using VB 2005. Basically I want my program
to add delete or update records in an access database.

I am struggling with one or two aspects and i dont know if i going the best way about them.

1.The database or any supporting files/folders may be located on a network.
from a support point of view, i want to be able to browse local pc or network pc to set the
database path and connection string for the app to use.
Obviously "openfiledialog" is used, but i am really struggling to code it


2. even tho what i want to achieve is quite small on the scale of things,
should i consider using SQL db? would sql server express that be easy to install programmatically
or would end user have to try and figure that out?

hope this post makes some sense lol

Thanks to all that reply )

[mendhak]

1. You want to be able to dynamically specify the location of the MDB. This means that you have your connection string, the only thing you'll be doing is setting the path to the MDB dynamically in your connection string by passing the string the OpenFileDialog's FileName property.

2. If you want your application to be scalable and robust and if you expect the application to grow over time, then nip the worm in the... cyst... and go for an enterprise level database server such as Oracle, SQL Server or MySql. In such a case, you won't be able to get the user to specify the 'location' of the database. It'll just have to be there ready to use. It would sit in one central location on the network.

[Hack]

Who would be you application audience?

In other words, for whom are you writing this program?

[robbie73]

Firstly, Thankyou both for quick responses.

Mendhak: I'm not sure that this particular application would ever be used enough to justify the expense of cutting a bit of worm off , but i was hoping to learn more about SQL and perhaps ONE day, maybe even making a commercial app ??

Hack: It's a small project with only one or two users, (basically, excel spreadsheets are getting a chore for users), so wanted to use a database.

I have for want of a better word "straightened" up the data in the .xls and got it into access. thats looking good and dandy. What i wanted was to build an app that could be scaled up as needs require, rather than just make some impressive forms in Access. (it will also strengthen my coding skills.)



I've decided it's coding a "set data path to .mdb" button/form at runtime i need pls.

Thanks again

[mendhak]

Well, you can use MS Access for now, the essential coding skills that you want to improve - will improve whether you use this or something else. The only difference is that you're using a different namespace (System.Data.OleDb instead of System.Data.SqlClient) and you won't be using stored procedures as much (as you should).

One justification that you could possibly find is that if you have a whole bunch of applications, each one using a tiny database on its own, then that justifies buying SQL Server so that it can all be consolidated into one location under the control of a DBA. Management likes nothing more than a single point of failure dependent on a single person's whims and fancies.

[robbie73]

LOL. thanks mendak.

erm could still use the help with setting the path in the connection string at runtime tho... ie some example code... pls??

[Hack]

Why would you need it set at runtime?

Are you planning on moving the database all over the place?

Put it in a network folder, and leave it there.

[robbie73]

ok, so i "hard code" the db connection string into the app.
installer places the files(ie ,database, photos, linked docs templates for mailmerging etc) into a network drive. gr8

Then, out of the blue, the network dies on one machine due to lan issues (or some other minor hardware problem. can u see where i going with this.?

The user obviously can't UPDATE the MAIN database, (don't want two people updating two diff db's..it stands to reason.)

BUT copying the db and files locally (using a usb drive or other such media) would give the poor end user with network issues the ability to at least VIEW recent data, thus enabling to carry out a days work or whatever without tons of questions flying back and forth to the colleague, thus stressing the colleague too...... and thats why....well, one example of why,... I dont want to "hard code" the path to the database.

(and please can somebody help with the code to dynamically select a path to DB, because that is what i really want )

[mendhak]

Fair enough, here's what you do. Hardcode your connection string in the code, but concatenate it. For example,

Dim MDBPath As String = "C:\test.mdb"
Dim ConnString As String = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=##MDBPath##;User Id=admin;Password=;
ConnString = ConnString.Replace("##MDBPath##",MDBPath)

See what I did there? You can therefore get MDBPath from an OpenFileDialog if you want, anywhere.

[robbie73]

Thankyou mendhak Spot on what I am looking for. I shall be giving this a crack sometime over the next couple of days and will no doubt have more questions.. be prepared
Also thankyou hack, your comments helped make me give the overall project some serious thought about what i wanted to achieve. As i know the person/people i am developing the app for, i have a good understanding of their circumstances, abilities and requirements.

It does pay to not only listen to the end user, but also your peers, who can give opinions and thoughts to good ideas that probably have been overlooked without them.

thanks guys

[GaryMazzone]

I generally store the location (Path) to the mdb in a config file (config.ini) saved in the applications folder. That way the network share name can be what ever the user want it to be. On initial startup of the app on a machine I have the user browse to the database (mdb file) location and store it in a var and write it to the ini file. On subsequent startups I read the ini file and test if I can find the database there if I can I open the app and continue. If the mdb has been moved I have the user browse to the new location save it and continue startup.

[mendhak]

Ah, good. But, but, but... you can now use Application Settings if you have VS 2005.

http://msdn2.microsoft.com/en-us/lib...xh(VS.80).aspx