Database Question

  • Thread starter Thread starter News
  • Start date Start date
N

News

Hello, I have to build a program with the future in mind and I need a bit
of guidance from a guru or two. My program will start as a multi-user
Windows Application built with VB.Net and using an Access 2002 database
backend. The future will require that 1. The database be switched with
minimal effort to SQL Server and 2. A Web Application be added to allow web
access to reports generated from the database. At this time, there is no
time line set for when 1 or 2 will come or whether 1 will come before 2.
The key for me is to program the initial application with the future firmly
in grasp so I can reuse code for the web app and upgrade the database
without recoding.

I have had Visual Studio .Net for 2 days and my head has been spun into many
tiny knots. What I have understood is that I can use Access 2002 but I
should use it in Microsoft Server Desktop Engine (MSDE) if I want to
upgrade. Is this info correct?

I have used VB as a front end and Access as a backend in the past and have
always gone with unbound controls. Choosing to make classes that handle
getting/adding/editing/updating records rather than binding using the data
environment. Is this the way to go now with .Net? Or is that a contentious
issue where you are either for databound or against databound programming?

I hope my question(s) aren't too general or unintelligible but as with any
'new' language the first days are the hardest days.

Thanks,

Gerry Dyck
 
I'm not a guru, but I have $0.02 . If you wnat to upgrade to SQL without
recoding anyting I would use MSDE, it uses the SQLClient class vs the OLEDB
class. You can still use a web front end with MSDE and it's much faster. As
for binding the controls that is completely up to you.
 
Gerry,

I would suggest using MSDE for a few reasons:
(1) There are a number of differences between the SQL syntax, for instance
you can use imbedded VBA directives in Access that you cannot do in SQL
Server (MSDE)
(2) With MSDE you have: Stored procedures, Views and Functions
(3) MSDE security is better then Access
(4) The upgrade path from MSDE to SQL Server is minimal

In terms of bound controls that all depends on your domain problem. dotNet
now exposes dataBinding as and interface so you could implement it in your
business classes rather then directly binding to a dataset. In my opinion
when using a facility like databinding you give up some flexibility for ease
of development. Bascially it is your call, but dotNet gives you more
flexibility then before...

Good Luck,
Dan
 
Hi Gerry,

I do a lot of database programming, both Access and SQL Server 2000 (more
the latter) using vb .net. Yes, MSDE for sure, although I would opt to
start with SQL Server, but that may not be your call (but put some heat on
the boss).

Data binding is not a contentious issue. I sometimes use data binding, but
more often do not, as I can be more efficient without it in many instances.
Its use for visual representations of data as you continually select is
valuable.

Grids can be very important in .net. The standard that comes with vs is
fine, but not easily manipulated for most real world needs. You may want to
look into something like the Component One True DB Grid, which I use - a
difficult learning curve but well worth it.

Also, pick up a few good books on vb .net and ado .net - Balena, MS Press,
Programming vb .net and Sceppa, also MS press, ado .net. There are also
several titles from Apress that are valuable, especially the books by
Vaughn, Troelson and Appleman.

HTH,

Bernie Yaeger
 
Gerry,
Having just finished converting an application that used an Access
backend to SQL Server, I'd say start with MSDE. You can always connect to
the database using an Access Data Project to look at data that way if you
want but you will be better off starting as you wish to continue.
Just as a note a long involved calculation on my project using real data
has gone down to 4.5 minutes from 9.5 minutes which is mostly attributed to
using stored procedures with the execution plans cached in the server. In
my case the memory load has also decreased because I was able to do more
complex queries using MSDE/SQL Server which save me from doing some
intermediate steps.

Ron Allen
 
Thanks for your response Ron as well as Jared, Dan and Bernie.

I seem to be going the right direction. Now I have another question. I
loaded MSDE but I cannot seem to get a connection in VB.Net. In the
connection wizard I am not getting any Server to connect to! I am having
the same problem when I go to "Upsize" my Access database. I am missing a
critical step to allow me to gain access to my MSDE server and of course the
documentation sends me in an endless loop with any help from the new
try-catch commands.

Thanks,
Gerry
 
Gerry,
Is the server running? Are all the services started? I have that "problem"
when trying to connect to MSDE through the connection wizard when I try to
use the drowdown. All you have to do is type in your MSSQLServer name in the
Select or enter a server name box. If that doesn't work try to make the
connection using code.

Try something like this (I'm sure there is a better way to do this but it's
only to test it):

create a button on a form and in the button_click event add

Dim db As New MyCompany.Layers.Data.Database
db.DataBaseName = "" 'Enter your database name here
db.ServerName = "" 'Enter your server name here
db.Connect()
If db.IsConnected Then
MsgBox("Connection Successful")
db.Disconnect()
Else
MsgBox("Connection Unsuccessful")
End If


Create a new class and copy and paste this over your class
----------------------------------------------------

Imports System.Data.SqlClient

Namespace MyCompany.Layers.Data

Public Class Database

Dim mDB As SqlConnection

Dim mDBName As String
Dim mServerName As String
Dim mConnectionString As String

Public Property DataBaseName() As String
Get

End Get
Set(ByVal Value As String)
If Not Value Is Nothing Then Me.mDBName = Value.TrimEnd
UpdateConnectionString()
End Set
End Property
Public Property ServerName() As String
Get
Return Me.mServerName
End Get
Set(ByVal Value As String)
If Not Value Is Nothing Then Me.mServerName = Value.TrimEnd
UpdateConnectionString()
End Set
End Property
Public Sub New()
UpdateConnectionString()
Me.mDB = New SqlConnection(Me.mConnectionString)
End Sub
Private Sub UpdateConnectionString()
Me.mConnectionString = "Data Source=" & Me.mServerName & _
";Initial Catalog=" & Me.mDBName & ";Integrated
Security=SSPI;"
Me.mDB.ConnectionString = Me.mConnectionString
End Sub
Public Function IsConnected() As Boolean
If Me.mDB.State <> ConnectionState.Open Then
Return True
Else
Return False
End If
End Function
Public Sub Connect()
Try
Me.mDB.Open()
Catch ex As Exception
MsgBox("Error connecting " & ex.Message)
'Error handling here
End Try
End Sub
Public Sub Disconnect()
Try
Me.mDB.Close()
Catch ex As Exception
'Error handling here
End Try
End Sub

End Class

End Namespace
 
Hi Jared.

I've gone into Control Panel - Administrative Tools - Component Services
and the MSDE Instance I created (actually did two) was started (for example
MSSQL$GERRY) as well I started service called SQLAgent#GERRY. I manually
enter MSSQL$GERRY in the Select box of the connection wizard and it still
doesn't work.

At the same time, I want to convert an MSAccess database to use in my
program. So I try using the Upsizing wizard. Again I manually enter
MSSQL$GERRY and I get an error window "Microsoft SQL Server Login"

Connection failed:
SQLState:'01000'
SQL Server Error:53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect()).
Connection failed:
SQLState:'08001'
SQL Server Error:17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied

For Pete's sake, I just want to program in VB.NET to a database. This is
quite frustrating as I haven't learnt anything about .NET and I have had the
program for a week. UUUGGGH.

Maybe there is some kind of primer on using MSDE or something that I haven't
found yet.

Thanks for help Jared,
Gerry
 
Back
Top