Changing the place of a Database

  • Thread starter Thread starter Luis Mendes
  • Start date Start date
L

Luis Mendes

Hello,

I've a application in VB.NET that uses a connection to a
Access data base stored in my Hard Drive.

How can I change the location of the database (To a
network) and change the conection string?

In VB6 this was very easy, but under VB.NET I'm not able
to do it.

Thanks in advance.

Luis
 
* "Luis Mendes said:
I've a application in VB.NET that uses a connection to a
Access data base stored in my Hard Drive.

How can I change the location of the database (To a
network) and change the conection string?

In VB6 this was very easy, but under VB.NET I'm not able
to do it.
Click to expand...

What's the problem? How do you connect to the database?
 
Hi Luis,
herfried is much more experienced than me but let me throw my two cents in.
Listen to Herfried though as he knows a lot more than I do.

What you can do is assign a string to the database path the user chooses
through an open file dialog box. Make sure you make it a public variable so
you can use it throughout your application.
Assuming the public string is called "databasepath" your connection string
could then look like this:

Dim sConn As New OleDb.OleDbConnection
sConn.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Jet OLEDB:Database Password=;Data Source=" & databasepath & _
";Password=;Jet OLEDB:Engine T" & _
"ype=5;Jet OLEDB:Global Bulk
Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Je" & _
"t OLEDB:System database=;Jet OLEDB:SFP=False;Extended
Properties=;Mode=Share Den" & _
"y None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System
Database=False;J" & _
"et OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without
Replica Re" & _
"pair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False"
 
Hi Scorpion,

The problem with this is that if you have made your connection string by
hand, you can do everything you want with it.

But when you use the generic tools, as far as I know till now, you cannot do
anything with it.

The sequence in that part is something like:
- connection string
- open connection

And you cannot come between that in the designer part.
When you change something, the designer corrects it full automatically for
you, if you want to change the slightest thing, the next time.

Therefore I think that designer thing is useless for commercial software.
Inside an organisation it will maybe work, if you are able to put from the
start on your database on the end place.

I follow this kind of messages like a hawk, but I have seen no good answer
till now.

Cor
 
Sorry you didnt like it.

I just took the code generated with the creation of the connection string
and replaced the path with the database variable.
xml file loaded at startup provides this info.

The only downside that I have encountered is you can't put it in a module as
I kept getting intialization errors. You might have a thought on that I
could use.

I had to do encryption(the whole string and then grabbing it out of an xml
file) to deal with the password problem in SQL and when one was required in
access but it worked. Sorry for the unnecessary code that is here but it is
what I had opened at the time.

'xml file for user choice of connection
<?xml version="1.0" encoding="utf-8" ?>
<Section Name="Settings">
<Key Name="databasepath" Value="C:\database.mdb"/> 'user fills this when
using open dialog box
</Section>

Loading the file at startup of app....

Private Sub ReadXmlConfig()
' Create Xml Document and load the xml file
Dim xmlDoc As XmlDocument = New XmlDocument
xmlDoc.Load("C:\Neumann Plumbing and Heating\XMLDBPATH.xml") 'can
use appdirectory when finished with project.
' Read mail server value
Dim keyNodeList As XmlNodeList =
xmlDoc.DocumentElement.SelectNodes("Key")
Dim keyNode As XmlNode
For Each keyNode In keyNodeList
' Read the attributes...
Dim attribs As XmlAttributeCollection = keyNode.Attributes
Dim attrib As XmlAttribute = attribs("Name")
If attrib.Value = "databasepaths" Then
databasepath = attribs("Value").Value.ToString()
'MsgBox(databasepath)
End If
Next
End Sub

Using the connection:

Sub GetComboBoxFills()
Dim sConn1 As New OleDb.OleDbConnection
sConn1.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Jet OLEDB:Database Password=;Data Source=" &
databasepath & _
";Password=;Jet OLEDB:Engine T" & _
"ype=5;Jet OLEDB:Global Bulk
Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Je" & _
"t OLEDB:System database=;Jet OLEDB:SFP=False;Extended
Properties=;Mode=Share Den" & _
"y None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System
Database=False;J" & _
"et OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without Replica Re" & _
"pair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False"

Dim itemcheck As Integer
Dim dscustomers2 As DataSet 'customer company
'more datasets not relevant here

dscustomers2 = Dscustomers1.Clone

Dim SQL3 As String
'more strings not relevant here

SQL3 = "SELECT DISTINCT CustCompanyName FROM CUSTOMERS order by
CustCompanyName"
dscustomers2.EnforceConstraints = False

Dim OleDbCUSTCOMPANY As New OleDb.OleDbDataAdapter 'customer COMPANY
OleDbCUSTCOMPANY = OleDbDBCUST

OleDbCUSTCOMPANY = New OleDb.OleDbDataAdapter(SQL3, sConn1)

OleDbCUSTCOMPANY.Fill(dscustomers2.Tables(0))
'more fills not relevant here

dscustomers2.AcceptChanges()

sConn1.Close() 'very important
 
Hi,

I just want to ask why you would want to do this instead
of setting a system DSN. The problem I have with this is
you have to rebuild your app every time you make a change
your path or even database password

Another alternative may be going back to the old ini file
(text file), which you can upload onto users machines upon
change without recompiling your source and reinstalling on
user machines.

I've had to go with system dsn so far, but haven't found a
more flexible option.
 
You are right Mike.

It is not efficient. But it is one that allows me to be able to completely
control what happens when the connection is called on another users machine.

If this stayed on my machine and was going no further yes you are right DSN
is the way to go. But the end users environment is very hard to predict what
will happen. This method lets them control the location and allows me full
control over the other aspects of the connection.

The other problem I encountered was when I needed to hide a password. With
encryption, I could hide it sufficinetly. Other methods I found lacking.
 
Mike,
You would not have to "rebuild" the app. The location of the database is set
in a xml file and is loaded on form load. The connection string will then
contain that variable as the physical location of the database.
 
Back
Top