Hi. Please forgive me for my newbie questions, but i am struggling with something and i need advice of an expert.

Basically, I am making a program that reads, reports on & writes to a database. Becuase of the nautre of the program, the datbase in question could be anywhere over a network, so from a support point of view, i need to be able to tell the program where to look to find the database, using a browse method. the user will click
"set drive for (name of program) database." and browse to the network or local machine. this will then be set in a table "drive dir" for the next time the program starts.

can somebody help me with the code for "set drive/path for database"

am working with vb 2005 and ms sql (.mdf database)

Thanks for any replies.

Rob

Here is an Egghead article that should help.

http://www.eggheadcafe.com/articles/20060719.asp





Best Regards,
Earl Francis

That's a useful article, however...

I can authoritatively state (because I just recently tried this) that you will be unlikely to get a SQL server database engine to connect to a networked MDF file. Because of the way SQL server reads and write data to and from the file, it has to be a local file.

-Peter

Thanks for your replies.:)
so it won't work with sql server to mdf over a network.??

hmm, has anybody any other possible solutions then? We can't go on using access 97!! lol, the program is creaking under the weight as it is, even with high spec machines/networks. and access 97 falls over alot when put under any decent workload.

thanks in advance

Well, while you can not connection SQL to a networked file, there's nothing to stop you from copying the MDF file to your local machine and attaching to it that way.

-Peter

ahh. good thought, but with multi users, its going to be a pain for them to see the changes and place the file back to its location without them all having to restart the application.
network congestion would also be an issue. sending a whole database back and forth for each change to records etc is really not ideal.

what do people use to read and write data over a network normally?

Currently we lock three records while being edited. the record to be changed and one either side of it in the table. can the rcords be locked, extracted and changed and then placed back before unlocking them again??

Well frankly, what you normally do is run a database server that all the applications talk to. It sounds like you are building a client/server app, so you should architect it as such. Even if you need to work with many databases you can build the app to handle that by allowing the user to select from a list of databases. This list could come from a single management database, or be part of a application config file. Any number of ways.

Is there a reason you are building this with a file based database approach?

-Peter

 because of deployment and enviroment variables. although the DRIVE DIR will always be present on the client machine (c:), as will a blank database for single users, networked clients will have different mapped drives and different configs.
the app runs anywhere, and if more than one user uses it, then they are going to want to be looking at the same data.
i not doing thier hardware or config thier network.
 i providing a program to reach the database, where they can then view edit and write to and delete records.

so long as the machines are networked, or terminal server, i want to be able to browse to the data and whereever it is on the network, and set that location as the path.

if the data gets moved, i want to be able to browse via the application to set the new data location, thus writing it to the DRIVE DIR table.

 does that make sense?

Ok. I think I see where you are going with this. The fact remains however that you can't connect a SQL Server DB engine to a networked file. And if you want to support several users accessing the data at the same time you will run into problems if you use another type of data file (such as XML). You will encounter all kinds of file locking issues.

It sounds like you are trying to build a client-server app with offline capabilities. This is no trivial task. (And given that you are posting in a beginner's forum category I can only assume that you are a beginning).

What type of application is this going to be? What will it do? Is it possible that there already exists some software that does this and handles the offline functionality that you desire? Perhaps if you describe a bit about the intent of the application we can help you better.

-Peter