Create DB Example - It works, but is it right?

[Miro]

I am teaching myself vb, and I was wondering if someone can check my code.
-It works, but i was wondering if i was on the right track or if I should
have done it differently.
-Again - thanks in advance to everyone who has helped me so far and will
help me again.

My goal last night was to create an mdb file. ( not through sql server )
just with ado. ( i think i got that terminology right )
-Tonights goal will be to add tables into the mdb file.

Anyway, i followed the example on this link:'
http://www.freevbcode.com/ShowCode.asp?ID=5797

and wrote my own code ( below )

-Here are my questions:
( Keep in mind my basic goal after i teach myself vb is to be able to create
a small vb.exe that will create and use
its own database that will be installed with an install shield - basically a
dummy prog so i can get the feel of a small
dummy application from start to finish. )

In the example, it says to add the Reference from the "COM" tab.
Should I be using something from the .NET tab? What is the difference?

In the Example it said to use the 2.7 ( but I selected 2.8 ) - Again it
worked, but not sure what the difference is.
I am thinking the answer is - no difference?

Any other suggestions ?

Thank You,
Miro

============ Code Example to please check =========

' I also had to add a Reference from the "COM" tab.
'Microsoft ADO Ext. 2.8 for DLL and Security.

' To create the main mdb file. ( Tables will be created in another
function )
Function CreateRequiredDB() As Boolean
Dim lFileExists As Boolean = False

' Without this "If statement" the Exception will catch if the file
already exists.
If Dir$("bla.abc") <> "" Then 'Returns the file name if it exists.
lFileExists = True
Else
Dim catNewDB As New ADOX.Catalog()
Dim sCreateString As String
sCreateString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=bla.abc" ' & DatabaseFullPath
Try
'Tries to Create the DB
catNewDB.Create(sCreateString)
lFileExists = True 'Created the new DB

Catch e As Exception
'Later write a function to write this to a text error log.
MsgBox("An Exception Occurred - " + e.Message)

lFileExists = False

Finally
catNewDB = Nothing
End Try

End If

Return lFileExists
End Function

 

[Cor Ligthert [MVP]]

Miro,

We have a sample as this as well.
http://www.vb-tips.com/default.aspx?ID=e76b8450-4c8a-4662-8f41-d6dda3c888c8

Included is how to make tables in this sample. It uses only Com for the part
of the creation. If you use a Com, you are not really using Net you use a
DLL which has more chance on a problem than using solely Net. There is in
Net however no method to create an Access Database. If you are learning I
point you on the SQLExpress database. In my opinion is it better to use that
to study.

However I will give some comments on your program inline

Dim lFileExists As Boolean = False

' Without this "If statement" the Exception will catch if the file
already exists.
If Dir$("bla.abc") <> "" Then 'Returns the file name if it exists.
lFileExists = True

Above is created the in Net included standard FileInfo class

Dim fi As New IO.FileInfo("c:\db1.mdb")
If fi.Exists Then

I advice you to use standard methods, which cannot give misunderstanding
when you are doing maintenance or give the program to others.

Dim sCreateString As String
sCreateString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=bla.abc" ' & DatabaseFullPath
catNewDB.Create(sCreateString)

A lot of work for a one time operation while it adds nothing (only
misunderstandings by those who read your program, what is beneath is at
least as good as)
catNewDB.Create( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=bla.abc" ' &
DatabaseFullPath)

I am not sure if the catNewDb.Create can throw an exception, if that is so
than it is better to use it, however do never this

Catch e As Exception

The "e" is standard used in all methods for events, therefore almost any
word is better than "e". By instance "ex".

'Later write a function to write this to a text error log.
MsgBox("An Exception Occurred - " + e.Message)

Try to avoid the + to concatenate Strings. For that is in VB the &. There
are situations where using the + can go wrong.

lFileExists = False

What is the sense of this above, I don't see it

catNewDB = Nothing

Setting something to nothing in VBNet at the end of a method has no sense at
all, than that it cost time. (This is classic VB use)

I hope this gives an idea.

Cor

 

[AlanT]

This is the sort of review I would give for an internal code review.

Some of the comments are purely stylistic - they are based upon our
coding standards, which although they are basically the MS VB.Net
standards, are not absolute.

Others are functional/design and are more generally applicable.


Formatting/naming et al.
-----------------------------------

1 ) Hungarian Notation has been dropped as a standard - it is
considered cumbersome and not really as useful anymore as the IDE is
more capable (e.g. hovering over a variable will tell you its type)


2) IFileExists - local variables should be camel cased
e.g.
Dim createString as string


[Company Standards]

Unless there is no VB.Net equivalent, the VB6 versions of functionality
should not be used.

e.g.
If you want to check if a file exists, use System.IO.File.Exists()

The reasoning behind this is that as long as people continue to use the
old functionality, they (in general) limit themselves to that
functionality. As mentioned, this is an internal company standard and
the more general population may disagree.

No literal constants within the code body. The db creation string
"Provider=..." and the "bla.abc" (if required) should be declared as
constants at the top of the routine. It is probably overkill here but
in generally, helps with localization et al. (not, I know a hugh
concern to everyone) and simplifies maintenance a tad as there is no
need to hunt through code.



Functionality/Design
-----------------------------

1) It looks like DatabaseFullPath is a global scope (or class scope, if
this method is in a class). It should be passed it. The more external
references in a method, the more problematic testing and maintenance
becomes.

1.1) "bla.abc" - what is this? Is it intended to change with each
DatabaseFullPath ?

2) This can possibly be a sub rather than a function. It depends on
whether or not you care if the db already exists. If you don't care,
then the only issue is when you try to create one and it fails. That
is an exception and should be handled as one rather than with a return
code.

3) This is not a presentation layer function, it shouldn't perform any
screen display - the Msgbox() shouldn't be here.

4) Following on from 3, this is not the best place for catching the
exception. It should be allowed to propagate up.

5) The Finally clause does nothing useful here - catNewDB is a local
variable and will automatically be cleared when the routine ends.


hth,
Alan.

 

[Miro]

Thank you Cor

That is exactly what I needed. Someone to go through it like that and let
me know what is right and proper and what is not.

-Extremely Helpful.
I don't know how people programmed / got help and info before the internet.

I think I will try to use a normal db table and then once I learn my way
around vb, hit the sql side of things.
I don't know the proper terminology and still have yet to make a form with
some db stuff on it.
Don't want to kill my brain all at once - So I will try SQL Express on
version 2.0

btw.
I return the lFileExists so that in my other function I might have to Code
around it if it was not
able to create the file ( for whatever reason ) and just load a splash
screen and say...try again later. Cant create db.
Or something

Thanks again,

Miro