Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.basic.visual.misc > #1696 > unrolled thread

ADO Problem in VB 2010

Back to article view | Back to comp.lang.basic.visual.misc

Contents

  ADO Problem in VB 2010 Jason Bodine <jbodine1974@gmail.com> - 2013-01-20 23:20 -0800
    Re: ADO Problem in VB 2010 Jason Bodine <jbodine1974@gmail.com> - 2013-01-21 09:16 -0800
    Re: ADO Problem in VB 2010 Jim Mack <no-uce-ube@mdxi.com> - 2013-01-21 12:28 -0500
    Re: ADO Problem in VB 2010 Deanna Earley <dee.earley@icode.co.uk> - 2013-01-21 17:36 +0000
    Re: ADO Problem in VB 2010 "Mayayana" <mayayana@invalid.nospam> - 2013-01-21 12:47 -0500
      Re: ADO Problem in VB 2010 Jason Bodine <jbodine1974@gmail.com> - 2013-01-21 22:07 -0800
        Re: ADO Problem in VB 2010 "John Paine" <john.paine@bigpond.com> - 2013-01-22 18:32 +1030
        Re: ADO Problem in VB 2010 Deanna Earley <dee.earley@icode.co.uk> - 2013-01-22 09:23 +0000
        Re: ADO Problem in VB 2010 ralph <nt_consulting@yahoo.com> - 2013-01-22 08:15 -0600
          Re: ADO Problem in VB 2010 ralph <nt_consulting@yahoo.com> - 2013-01-22 10:38 -0600

#1696 — ADO Problem in VB 2010

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

[toc] | [next] | [standalone]

#1698

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

[toc] | [prev] | [next] | [standalone]

#1699

> 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

[toc] | [prev] | [next] | [standalone]

#1700

On 21/01/2013 07:20, Jason Bodine wrote:
> 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

-- 
Deanna Earley (dee.earley@icode.co.uk)
i-Catcher Development Team
http://www.icode.co.uk/icatcher/

iCode Systems

(Replies direct to my email address will be ignored. Please reply to the 
group.)

[toc] | [prev] | [next] | [standalone]

#1701

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. 

[toc] | [prev] | [next] | [standalone]

#1702

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




On Monday, January 21, 2013 11:47:05 AM UTC-6, Mayayana wrote:
> 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.

[toc] | [prev] | [next] | [standalone]

#1703

>
> "Jason Bodine" <jbodine1974@gmail.com> wrote in message 
> news:79d24a1f-b9fb-4d88-8717-4889fae955a7@googlegroups.com...
> 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

[toc] | [prev] | [next] | [standalone]

#1704

On 22/01/2013 06:07, Jason Bodine wrote:
> 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

> 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.

> 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.

-- 
Deanna Earley (dee.earley@icode.co.uk)
i-Catcher Development Team
http://www.icode.co.uk/icatcher/

iCode Systems

(Replies direct to my email address will be ignored. Please reply to the 
group.)

[toc] | [prev] | [next] | [standalone]

#1705

On Mon, 21 Jan 2013 22:07:44 -0800 (PST), Jason Bodine
<jbodine1974@gmail.com> wrote:

> 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

[toc] | [prev] | [next] | [standalone]

#1706

On Tue, 22 Jan 2013 08:15:50 -0600, ralph <nt_consulting@yahoo.com>
wrote:


>
>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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.basic.visual.misc

csiph-web