dcsimg
Results 1 to 20 of 20

Thread: Connection string problem

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Location
    South Africa
    Posts
    400

    Connection string problem

    Hi All

    Please could someone give me some advice on the following as i seem to have hit a blank. I use a global connection string with SQL server and this works like a charm. I am busy writing a small app that must use access as the database, so i am trying to duplicate my connection string for sql but to be used with access, but i can't seem to get it right.

    Here are my declarations in modDeclarations

    Code:
    'Connection Stings and Server Variables
    Public cnnMain As ADODB.Connection
    Public rs As New ADODB.Recordset
    Public us As New ADODB.Recordset
    Here is the declaration for modMain.

    Code:
    Public Sub Openconnection(Timeout As Integer, ServerName As String, UserName As String, Password As String, Database As String)
    ' Open a connection without using a Data Source Name (DSN).
        Set cnnMain = New ADODB.Connection
        cnnMain.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\EasiTot\Data\Data.mdb;"
        cnnMain.ConnectionTimeout = Timeout
        cnnMain.Open
        On Error GoTo 0
        Exit Sub
            End
    End Sub
    Public Sub ActiveReadServer(Query As String)
        If rs.State = 1 Then rs.Close
        rs.Open Query, cnnMain, adOpenStatic, adLockReadOnly
            Exit Sub
    End Sub
    Public Sub ActiveUpdateServer(Query)
        us.Open Query, cnnMain, , adLockBatchOptimistic
            Exit Sub
    End Sub
    Sub Main()
        Form1.Show
    End Sub
    I get an error in this line.

    Code:
     rs.Open Query, cnnMain, adOpenStatic, adLockReadOnly
    The error is "This connection cannot be used to perform this operation. It is either closed or invalid in this context"

    Please could someone point me in the right direction.

    Many thanks in advance

  2. #2
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,698

    Re: Connection string problem

    you can not use the same connection object for sql and access unless you close, destroy, and reinitialize a new connection/open.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Location
    South Africa
    Posts
    400

    Re: Connection string problem

    Hi RobDog

    Thank you for your reply, for the app i am writing i only need the connection string for access, or i think i have missunderstood you. Also how would i destroy the connection or is it better to code the entire connection string everytime i need to query the db. I'm a bit confused as what to do here.

    Many thanks

  4. #4
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Connection string problem

    What he is saying is that if you are going to connect to both databases, you really should use two, separate, connection objects.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  5. #5
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,698

    Re: Connection string problem

    If you only need the conectionstring for access then how are you going to connect to sql?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Location
    South Africa
    Posts
    400

    Re: Connection string problem

    Hi Guys

    Sorry i think i explained like an idiot, i only need the access connection string i'm not going to use sql at all for this app. I normally only use sql so that is what i am used too. So basicaly all i am trying to do is create a public connection string to my access db so that i dont have to code the connection everytime. But i get errors and can't seem to figure out what i'm doing wrong.

    Many thanks and sorry for the confusion.
    AJ

  7. #7
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Connection string problem

    Try this
    Code:
    Dim ADOCn As ADODB.Connection
    Dim ConnString As String
    
    ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\EasiTot\Data\Data.mdb;" & _
            "Persist Security Info=False"
    
    Set ADOCn = New ADODB.Connection
    ADOCn.ConnectionString = ConnString
    ADOCn.Open ConnString
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Location
    South Africa
    Posts
    400

    Re: Connection string problem

    Hi Hack

    Sorry i am being a doofus, i cant seem to figure out how to make this connection string public, or what to use as my recordset. I'm used to using ActivReadServer and ActivUpdateServer. Please help, it's frustrating that i can't get this.

    Many thanks

  9. #9
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Connection string problem

    In a module, do
    Code:
    Public ADOCn As ADODB.Connection
    Public adoRS as ADODB.Recordset
    Public ConnString As String
    Is this what you mean?
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  10. #10
    PowerPoster
    Join Date
    Feb 2006
    Location
    East of NYC, USA
    Posts
    5,692

    Re: Connection string problem

    Are you calling Openconnection before you call ActiveReadServer? BTW, ActiveUpdateServer should be
    Code:
    Public Sub ActiveUpdateServer(Query)
    cnnMain.Execute Query
    End Sub
    Also, declare your objects (connection, recordset) without the New. When they're used,
    Set <object> = New <type of object>
    Then when you're through using an object, you can destroy it with
    Set <object> = Nothing

    If you don't destroy an object, the memory allocated to it remains used - even after the application ends. It's a common source of memory leaks. And you can't destroy an object declared as New, only one Set as New.
    The most difficult part of developing a program is understanding the problem.
    The second most difficult part is deciding how you're going to solve the problem.
    Actually writing the program (translating your solution into some computer language) is the easiest part.

    Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read.

    Please Help Us To Save Ana

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Location
    South Africa
    Posts
    400

    Re: Connection string problem

    Hi Guys

    Thank you for your help but for some reason i am really struggling with this, i have made the suggested changes as follows, but now get a different error. My public connection string knowledge clearly needs alot of work on my part. Here is what i have now in modMain.

    Code:
    Public Sub Openconnection(Timeout As Integer, ServerName As String, UserName As String, Password As String, Database As String)
    ' Open a connection without using a Data Source Name (DSN).
        cnnMain = New ADODB.Connection
        ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\EasiTot\Data\Data.mdb;" & _
            "Persist Security Info=False"
            
        cnnMain.ConnectionTimeout = Timeout
        cnnMain.Open
        On Error GoTo 0
        Exit Sub
            End
    End Sub
    Public Sub ActiveReadServer(Query As String)
        If adoRS.State = 1 Then adoRS.Close
        adoRS.Open Query, cnnMain, adOpenStatic, adLockReadOnly
            Exit Sub
    End Sub
    Public Sub ActiveUpdateServer(Query)
        cnnMain.Execute Query
            Exit Sub
    End Sub
    Sub Main()
        Form1.Show
    End Sub
    Here is modDeclarations

    Code:
    'Connection Stings and Server Variables
    Public ADOCn As ADODB.Connection
    Public adoRS As ADODB.Recordset
    Public ConnString As String
    I get an error in ActiveReadServer: Object Variable or with block variable not set.

    Many thanks again for your help.

  12. #12
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,698

    Re: Connection string problem

    Are you calling Openconnection before ActiveReadServer?

    You dont need to change teh on error and the end statement as they both are of no use and not called.

    Code:
    Public Sub Openconnection(Timeout As Integer, ServerName As String, UserName As String, Password As String, Database As String)
    ' Open a connection without using a Data Source Name (DSN).
        cnnMain = New ADODB.Connection
        ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\EasiTot\Data\Data.mdb;" & _
            "Persist Security Info=False"
            
        cnnMain.ConnectionTimeout = Timeout
        cnnMain.Open
        'On Error GoTo 0
        'Exit Sub
            'End
    End Sub
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Location
    South Africa
    Posts
    400

    Re: Connection string problem

    Hi RobDogg

    Thank you for your reply, yes i open the connection with cnn.open but no matter what i do i still get the same variable not set error. Do you know of any tutorials that describe how to setup a public connection string. The reason being is that i dont really want to code the entire connection and everything everytime i need to query the database, so i thaught this way would make it easier, but i can't seem to get it right.

    Many thanks

  14. #14
    old fart Frans C's Avatar
    Join Date
    Oct 1999
    Location
    the Netherlands
    Posts
    2,926

    Re: Connection string problem

    In Openconnection you open connection cnnMain , but in your declarations you have declared ADOCn.

    Change either one of them to match.
    Also I don't see where you call the Openconnection sub.
    And you have a lot of unused parameters in your Openconnection sub.
    Frans

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Location
    South Africa
    Posts
    400

    Re: Connection string problem

    Hi Frans

    Thank you for your reply, i have made your suggested changes i think, but as you can see i dont really know what the hell i'm doing, as using modules is pritty new to me, and still can't figure out where i'm going wrong. I have attached a screen shot of the error, any advice is much appreciated.

    Kindest
    AJ
    Attached Images Attached Images  

  16. #16
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,325

    Re: Connection string problem

    Have you called the Openconnection sub before that one? (you need to)

    If you have, add the keyword "Set" to the start of the first line, eg:
    Code:
    Set ADOcn = New ADODB.Connection

  17. #17
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,698

    Re: Connection string problem

    He should be getting an error if he was calling the Openconnection sub first. So I still think its not being called. Also, as frans pointed out, the connection object is named differently in the Openconnection sub and the ActiveUpdateServer.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  18. #18

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Location
    South Africa
    Posts
    400

    Re: Connection string problem

    Hi Guys

    Thank you for all your help, i have tried to do what you guys have suggested but stilll get the same stupid errors even tried Call Openconnection but no luck, added some more active X objects in the library. Here is my code on form load.

    Code:
    Private Sub Form_Load()
    Call Openconnection
        ActiveReadServer "SELECT * FROM BottleSetup"
            Text1.Text = rs.Fields("Description")
    End Sub
    If all else fails i'll just code everything as i need. Many thanks once again for your patience.

    AJ

  19. #19
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Connection string problem

    Quote Originally Posted by alexanderjames
    If all else fails i'll just code everything as i need. Many thanks once again for your patience.
    So is this resolved or do you still have issues?
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  20. #20
    Member jeruelx5d's Avatar
    Join Date
    Jun 2006
    Posts
    39

    Re: Connection string problem

    Try this:
    See attach sample code...
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width