ADO Problem in VB 2010

[Jason Bodine]

Hi All!

I've been working to port an App I wrote in VB6 a while back to a .NET version using VB 2010. The program uses an Access database to store and retrieve information. My problem is, all I can get it to create is an empty database because for some reason, it doesn't seem to like the connection string and I can't figure out why. Below, I've pasted my entire module containing the Sub Main code. Could someone please take a look at it and tell me what's going wrong?

Thanks!
Jason

P.S. If this sounds familiar to anyone, it's because I a question about the same type of issues when I was using VB6. This code implements those solutions, but what's good for the goose apparently *isn't* good for the gander!! LOL
----------------------------------

Option Explicit On
Imports ADOX
Imports ADODB
Imports System.Data.OleDb


Module Start


Public cat As ADOX.Catalog
Public table As ADOX.Table
Public column As ADOX.Column
Public con As ADODB.Connection
Public cmd As ADODB.Command
Public rec As ADODB.Recordset
Public sConnection As String
Public DBPath As String
Public PicPath As String
Public Active_Profile As String
Dim Admin As Object

Public Function FileExists(ByVal fPath As String) As Boolean
If Dir(fPath) <> "" Then
FileExists = True
Else
FileExists = False
End If
End Function
Public Sub SetDB()
DBPath = Application.StartupPath & "\Profiles.mdb"
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBPath & ";"
End Sub
Public Sub StartDB()
con = New ADODB.Connection
con.Open(sConnection, "Admin")
End Sub
Public Sub StopDB()
con.Close()
con = Nothing
End Sub
Public Sub MakeDB()
cat = New ADOX.Catalog
cat.Create(sConnection)
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBPath & ";Jet OLEDB:Engine Type=5"
table = New ADOX.Table
With table
.Name = "User_Profiles"
.ParentCatalog = cat
column = New ADOX.Column
With column
.Name = "User_Name" '0
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar

End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Password_Protection_Enabled" '1
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adBoolean
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "TattleTale_Enabled" '2
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adBoolean
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "User_Password" '3
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Security_Question" '4
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Security_Answer" '5
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Email_Address" '6
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Mobile_Phone_Number" '7
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Mobile_Service_Provider" '8
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Mobile_SMS_Server" '9
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Mobile_MMS_Server" '10
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Email_Enabled" '11
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adBoolean
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "SMS_Enabled" '12
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adBoolean
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "MMS_Enabled" '13
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adBoolean
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Default_Wallpaper" '14
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Document_Background_Color" '15
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Document_Font_Color" '16
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Default_Font" '17
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Default_Font_Size" '18
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Document_Left_Margin" '19
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Document_Right_Margin" '20
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Default_Profile" '21
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adBoolean
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Toolbar_Visible" '22
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adBoolean
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Options_Toolbar_Visible" '23
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adBoolean
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Status_Bar_Visible" '24
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adBoolean
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Timestamp_Entries" '25
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adBoolean
End With
.Columns.Append(column)
End With
cat.Tables.Append(table)
table = Nothing
table = New ADOX.Table
With table
.Name = "User_Files"
.ParentCatalog = cat
column = New ADOX.Column
With column
.Name = "File_Name" '0
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Path_To_File" '1
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Date_Created" '2
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "Date_Last_Modified" '3
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "File_Size" '4
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "File_Content" '5
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "File_Author" '6
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adVarWChar
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "File_Password_Enabled" '7
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adBoolean
End With
.Columns.Append(column)
column = New ADOX.Column
With column
.Name = "File_Password" '8
.ParentCatalog = cat
.Type = ADOX.DataTypeEnum.adBoolean
.Attributes = ColumnAttributesEnum.adColNullable
End With
.Columns.Append(column)
End With
cat.Tables.Append(table)
cat = Nothing
End Sub
Public Sub MakeAdmin()
cmd = New ADODB.Command
With cmd
.ActiveConnection = con
.CommandText = "SELECT * FROM [User_Profiles]"
.CommandType = CommandTypeEnum.adCmdText
End With
rec = New ADODB.Recordset
With rec
.CursorType = CursorTypeEnum.adOpenStatic
.CursorLocation = CursorLocationEnum.adUseClient
.LockType = LockTypeEnum.adLockOptimistic
.Open(cmd)
.AddNew()
.Fields(0).Value = Admin
.Fields(1).Value = False
.Fields(2).Value = False
.Fields(3).Value = ""
.Fields(4).Value = ""
.Fields(5).Value = ""
.Fields(6).Value = ""
.Fields(7).Value = ""
.Fields(8).Value = ""
.Fields(9).Value = ""
.Fields(10).Value = ""
.Fields(11).Value = False
.Fields(12).Value = False
.Fields(13).Value = False
.Fields(14).Value = ""
.Fields(15).Value = ""
.Fields(16).Value = ""
.Fields(17).Value = ""
.Fields(18).Value = ""
.Fields(19).Value = ""
.Fields(20).Value = ""
.Fields(21).Value = True
.Fields(22).Value = True
.Fields(23).Value = True
.Fields(24).Value = True
.Fields(25).Value = True

.Update()
End With
rec.Close()
cmd = Nothing
rec = Nothing
End Sub
Public Sub CheckForProfiles()
DBPath = Application.StartupPath & "\Profiles.mdb"
con = New ADODB.Connection
With con
.Provider = "Microsoft.Jet.OLEDB.4.0"
.CursorLocation = CursorLocationEnum.adUseClient
.Mode = ConnectModeEnum.adModeReadWrite
.Open(DBPath)
End With
cmd = New ADODB.Command
With cmd
.ActiveConnection = con
.CommandText = "SELECT * FROM [User_Profiles]"
.CommandType = CommandTypeEnum.adCmdText
End With
rec = New ADODB.Recordset
With rec
.CursorType = CursorTypeEnum.adOpenStatic
.CursorLocation = CursorLocationEnum.adUseClient
.LockType = LockTypeEnum.adLockOptimistic
.Open(cmd)
If .RecordCount > 1 Then
frmSelect.Show()
Do Until .EOF
frmSelect.cmbSelect.Items.Add(.Fields(0))
.MoveNext()
Loop
ElseIf .RecordCount = 1 Then
frmSelect.Show()
With frmSelect
.btnLoadProfile.Enabled = False
.lblSelect.Text = "Please enter a name for your new profile and click Create Profile."
End With
Else
MsgBox("No Records Found!", vbOKOnly + vbInformation, "Error")
End If
.Close()
End With
rec = Nothing
cmd = Nothing
con.Close()
con = Nothing
End Sub
Public Sub Main()
SetDB()
If Not FileExists(DBPath) Then
MakeDB()
StartDB()
MakeAdmin()
StopDB()
SaveSetting(Application.CompanyName, Application.ProductName, "Active_Profile", "Admin")
Active_Profile = GetSetting(Application.CompanyName, Application.ProductName, "Active_Profile")
CheckForProfiles()
Else
CheckForProfiles()
End If
End Sub
End Module

[Jason Bodine]

More info:

It doesn't seem to want to accept a string as a valid argument for cat.ActiveConnection. Doesn't matter whether I type it out or set it to the sConnection variable. How do I fix this????

Thanks!
Jason

[Jim Mack]

Post by Jason Bodine
Hi All!
I've been working to port an App I wrote in VB6 a while back to a .NET
version using VB 2010.

This group is, by consensus, for 'classic' VB. You might get a stray
answer here for .NET questions, but they're discouraged. Better to find
a group dedicated to .NET and ask there.

--
Jim

[Deanna Earley]

Post by Jason Bodine
Hi All!
I've been working to port an App I wrote in VB6 a while back to a
.NET version using VB 2010. The program uses an Access database to
store and retrieve information. My problem is, all I can get it to
create is an empty database because for some reason, it doesn't seem
to like the connection string and I can't figure out why. Below,
I've pasted my entire module containing the Sub Main code. Could
someone please take a look at it and tell me what's going wrong?

From your code you seem to be using COM ADO in .Net.
Try using the native ADO.Net

[Mayayana]

In addition to Jim Mack's advice...

Given that your code seems to be almost entirely
dealing with the ADO control, which is not any kind
of VB particularly, why not try an Access group? Some
scripters and some VBers work with MS Office, but
it doesn't "go with the territory".

microsoft.public access

If you prefer a .Net group:

microsoft.public.dotnet.languages.vb

You can also look at these links for active MS web forums
that might apply:

http://social.microsoft.com/Forums/en-US/categories
http://social.technet.microsoft.com/forums/en-US/categories/
http://social.msdn.microsoft.com/Forums/en/categories/

And finally, this is the only MS web forum for scripting.

http://social.technet.microsoft.com/Forums/en-US/ITCG/threads

It gets question for all scripting, which makes it rather
confusing, but ADO is commonly used with Windows
Script Host, so you might get knowledgeable answers
there.

[Jason Bodine]

Hi,

Well, aside from the fact that *every single tutorial* when I type ADO.NET into Google uses the COM objects and uses the very type of code I posted here (and somehow it works for *them*, lol), the problem with using ADO.NET is that it's a lot more complicated than I'm used to. I am a Classic VB veteran, and in fact the application that I'm working on converting was originally written in VB6. Only reason I'm trying to port it over to 2010 is I need the added functionality of *that* language to apply some of the more major updates I have in mind for this version of the application. The database part, while vital its operation, doesn't need all that added hooplah. It just needs to be able to create the database and then store to and retrieve information from it.

Besides that, the people on this group have always been a lot more helpful than some of the others! I've come across some pretty elitist programmers before who seem to think if you don't already know what you're doing with whatever language they program in, then it's not worth their time to teach you.

Anyway, thanks for responding! I guess I'll keep looking and hope I can find *something* that works!

Jason

Post by Mayayana
In addition to Jim Mack's advice...
Given that your code seems to be almost entirely
dealing with the ADO control, which is not any kind
of VB particularly, why not try an Access group? Some
scripters and some VBers work with MS Office, but
it doesn't "go with the territory".
microsoft.public access
microsoft.public.dotnet.languages.vb
You can also look at these links for active MS web forums
http://social.microsoft.com/Forums/en-US/categories
http://social.technet.microsoft.com/forums/en-US/categories/
http://social.msdn.microsoft.com/Forums/en/categories/
And finally, this is the only MS web forum for scripting.
http://social.technet.microsoft.com/Forums/en-US/ITCG/threads
It gets question for all scripting, which makes it rather
confusing, but ADO is commonly used with Windows
Script Host, so you might get knowledgeable answers
there.

[John Paine]

Post by Jason Bodine
Hi,
Well, aside from the fact that *every single tutorial* when I type ADO.NET
into Google uses the COM objects and uses the very type of code I posted
here (and somehow it works for *them*, lol), the problem with using
ADO.NET is that it's a lot
more complicated than I'm used to. I am a Classic VB veteran, and in fact
the application that I'm working on converting was originally written in
VB6. Only reason I'm trying to port it over to 2010 is I need the added
functionality of *that* language
to apply some of the more major updates I have in mind for this version
of the application. The database part, while vital its operation, doesn't
need all that added hooplah. It just needs to be able to create the
database and then store to and retrieve
information from it.
Besides that, the people on this group have always been a lot more helpful
than some of the others! I've come across some pretty elitist programmers
before who seem to think if you don't already know what you're doing with
whatever language they
program in, then it's not worth their time to teach you.
Anyway, thanks for responding! I guess I'll keep looking and hope I can
find *something* that works!
Jason

Hi Jason,

This may not be relevant, but I recently encountered a problem with
connection strings in VB6 which was due to a change in Microsoft's security
policies. The change caused previously valid connection strings to no longer
work because security related content was removed from the connection string
that the ADO object returned. User name and password I could understand, but
so much of the string was removed that saving it so that the user could
reconnect easily the next time they used the program just didn't work.

The solution in my case was to add a flag to force the return of a usable
connection string by using the code:

MyADODB.Properties("Persist Security Info") = True

before doing the connection the first time (when the user manually selects
the required database) and then getting the connection string using the
code:

ConnectionString = MyADODB.Properties("Extended Properties").Value

The returned connection string then worked fine.

You could try using ADO to connect to an existing MDB file and then copy the
complete connection string with the new database name to see if that works
to allow you to add the tables you require. Once you get a working string,
you can then try trimming out components to get a minimal set that works.

Good luck!

John

[Deanna Earley]

Post by Jason Bodine
Hi,
Well, aside from the fact that *every single tutorial* when I type
ADO.NET into Google uses the COM objects and uses the very type of
code I posted here (and somehow it works for *them*, lol)

They do?
For me, the first is Wikipedia, the next 2 are official documentation,
the next two tutorials use ADO.Net natively.
http://csharp-station.com/Tutorial/AdoDotNet/Lesson01
http://www.codeproject.com/Articles/8477/Using-ADO-NET-for-beginners

Post by Jason Bodine
the problem with using ADO.NET is that it's a lot more complicated
than I'm used to.

You get that when you change languages and environment.
In this case, its not really more complicated, it's just different.
Exactly the same concepts apply.

Post by Jason Bodine
The database part, while vital its operation, doesn't need all that
added hooplah. It just needs to be able to create the database and
then store to and retrieve information from it.

That's pretty standard for a database library.

[ralph]

On Mon, 21 Jan 2013 22:07:44 -0800 (PST), Jason Bodine

Post by Jason Bodine
Well, aside from the fact that *every single tutorial* when I type ADO.NET
into Google uses the COM objects and uses the very type of code I posted
here (and somehow it works for *them*, lol), the problem with using
ADO.NET is that it's a lot
more complicated than I'm used to. I am a Classic VB veteran, and in fact
the application that I'm working on converting was originally written in
VB6. Only reason I'm trying to port it over to 2010 is I need the added
functionality of *that* language
to apply some of the more major updates I have in mind for this version
of the application. The database part, while vital its operation, doesn't
need all that added hooplah. It just needs to be able to create the
database and then store to and retrieve
information from it.

Deanne's suggestion to port to ADO.Net is probably best. But, if you
want to stay with the ADO COM/OLE solution, there is no reason it
shouldn't work as expected.

As John Paine noted, I too have discovered on occasion that 'less
complete' (for lack of a better term) connnection strings can fail on
newer Windows platforms and from within a managed environment.

For this reason it is useful to use the Data Link Properties dialog to
create, configure, and test ADO connection strings. This can be done
within YOUR specific problem domain, and cuts down on error-prone, hit
'n miss, re-typing.

Using Explorer go to the project folder.
Create an empty file with the .UDL extension. (eg, "myapp.udl").
Double-click on the file.
This will open the Data Link Properies dialog.
You should be able to figure out what to do from there.
Particualarly note that after you selected your options you can then
TEST the connection.

[There is also a .Net tool for working with .udl, but I still find
this simple method the easiest.]

At this point there are two ways to use the .udl.

1) You can use the udl file directly as in ...
sConnection = "File Name=c:\myproject\myapp.udl"
Handy if you want to change the connection outside the Application
during development or while in production.

2) Or simple open the .udl file in notepad (or with any text editor)
and copy the connection string directly into your application.

I'm still not totally convinced it is your connection string that is
the problem. Unable however to see any thing wrong with your code -
which simply means it is likely very obvious and I'm just blind - <bg>

hth
-ralph

[ralph]

Post by ralph
At this point there are two ways to use the .udl.

Should have noted that you can easily modify the .udl file in a text
editor to supply something the dialog interface doesn't directly
support.

And there is this alternative method which is also helpful while
developing an ADODB application to quickly ferret out connection
string problems.
[Warning Air Code!]

In your project reference the
"OLE DB Service Component Type Library"

In code do this ...

Dim conn As ADODB.Connection
Dim oDL As MSDASC.DataLinks
Set oDL = New MSDASC.DataLinks

Set conn = New ADODB.Connection
oDL.PromptEdit conn
' Note: code blocks here
' ie, dialog has to be closed before continueing

' Will error if [Cancel] is selected so check for empty
If conn.ConnectionString <> "" Then
conn.Open
...
' The connection object is now valid for use anywhere,
' as long as you don't re-set to Nothing.
' .Open and .Close as desired
' Note: .ConnectionString is read/write while conn object
' is closed, read-only if conn is opened.
conn.Close
Else
Debug.Print "No connection string created"
End If

hth
-ralph