Results 1 to 32 of 32

Thread: Open Different Data Source Name

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Open Different Data Source Name

    FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\BSParking.mdb;"

    As you can see the example above it opens BSParking.mdb,
    Is there a way to open different file name?

    And how can I add a Botton to Open a Explorer and search for that Data source?
    New Problem: http://www.vbforums.com/showthread.p...=1#post4614539
    Last edited by Cronqvist; Feb 11th, 2014 at 11:08 PM.

  2. #2
    PowerPoster
    Join Date
    Jul 2001
    Location
    Tucson, AZ
    Posts
    2,166

    Re: Open Different Data Source Name

    You must be new to programming.
    Is there a way to open different file name?
    Make "BSParking.mdb" a variable and you can put anything there you want.

    And how can I add a Botton to Open a Explorer and search for that Data source?
    Do some research on Common Controls

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    how to make it viable? im totally new Sire

  4. #4
    Default Member Bonnie West's Avatar
    Join Date
    Jun 2012
    Location
    InIDE
    Posts
    4,060

    Re: Open Different Data Source Name

    dw85745 probably meant something like this:

    Code:
    Option Explicit     'Drop a CommandButton & a CommonDialog control on a blank Form
    
    Private Sub Command1_Click()
        Dim sPathName As String
    
        With CommonDialog1      'Add the Microsoft Common Dialog Control 6.0 (SP6) via the Components dialog box
            .CancelError = True
            .Filter = "Access Database file (*.mdb)|*.mdb"
            .Flags = cdlOFNHideReadOnly
             On Error GoTo 1
            .ShowOpen
             sPathName = .FileName
    1   End With
    
        If LenB(sPathName) Then '<-- If sPathName isn't empty
            FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
        End If
    End Sub
    On Local Error Resume Next: If Not Empty Is Nothing Then Do While Null: ReDim i(True To False) As Currency: Loop: Else Debug.Assert CCur(CLng(CInt(CBool(False Imp True Xor False Eqv True)))): Stop: On Local Error GoTo 0
    Declare Sub CrashVB Lib "msvbvm60" (Optional DontPassMe As Any)

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    Sir Bonnie West thank you soo much!
    but where do i need to put this code? On my main form right?
    gonna try wait
    i need only 1 command button right?

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    I did put your Codes into my Main Form
    and add the code into my new added command button, sadly got Error Sir

    Compile error:
    Variable not defined

    Highlighted text: CommonDialog1

    Private Sub Command1_Click()
    Dim sPathName As String

    With CommonDialog1 'Add the Microsoft Common Dialog Control 6.0 (SP6) via the Components dialog box
    .CancelError = True
    .Filter = "Access Database file (*.mdb)|*.mdb"
    .Flags = cdlOFNHideReadOnly
    On Error GoTo 1
    .ShowOpen
    sPathName = .FileName
    1 End With

    If LenB(sPathName) Then '<-- If sPathName isn't empty
    FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
    End If
    End Sub

  7. #7
    Default Member Bonnie West's Avatar
    Join Date
    Jun 2012
    Location
    InIDE
    Posts
    4,060

    Re: Open Different Data Source Name

    Quote Originally Posted by Cronqvist View Post
    I did put your Codes into my Main Form
    and add the code into my new added command button, sadly got Error Sir

    Compile error:
    Variable not defined

    Highlighted text: CommonDialog1
    I believe my instructions were very clear:

    Quote Originally Posted by Bonnie West View Post
    Code:
    'Drop a CommandButton & a CommonDialog control on a blank Form
    
    'Add the Microsoft Common Dialog Control 6.0 (SP6) via the Components dialog box
    Don't know how you've missed that... Anyway, if you still need more directions, see Using the Common Dialog Control and especially the subsection Displaying Open and Save As Dialog Boxes. At some point in the future, you'll probably want to ditch the dependency on that ActiveX control. Let us know if you're seeking a replacement for that.



    P.S.

    Glad to know you're using Option Explicit!
    On Local Error Resume Next: If Not Empty Is Nothing Then Do While Null: ReDim i(True To False) As Currency: Loop: Else Debug.Assert CCur(CLng(CInt(CBool(False Imp True Xor False Eqv True)))): Stop: On Local Error GoTo 0
    Declare Sub CrashVB Lib "msvbvm60" (Optional DontPassMe As Any)

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    Its working fine =)

    I did put a Object CommonDialog and Command Button at my Main Form. When I run works fine
    and I can now open any .MDB File

    Problem
    Run-time error '3705':
    Operation is not allowed when the object is open.

    CODE Code:
    1. Private Sub Command1_Click()
    2.     Dim sPathName As String
    3.  
    4.     With CommonDialog1      'Add the Microsoft Common Dialog Control 6.0 (SP6) via the Components dialog box
    5.         .CancelError = True
    6.         .Filter = "Access Database file (*.mdb)|*.mdb"
    7.         .Flags = cdlOFNHideReadOnly
    8.          On Error GoTo 1
    9.         .ShowOpen
    10.          sPathName = .FileName
    11. 1   End With
    12.  
    13.     If LenB(sPathName) Then '<-- If sPathName isn't empty
    14.         FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
    15.     End If
    16. End Sub

    Highlighted Code
    FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    Last edited by Cronqvist; Feb 11th, 2014 at 11:07 PM.

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    Last edited by Cronqvist; Feb 11th, 2014 at 11:07 PM.

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name


  12. #12
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Open Different Data Source Name

    Before you try to re-apply the next Connection-String, you should ensure that your FindCon-Connection
    is closed (if it was opened before):

    If FindCon.State <> adStateClosed Then FindCon.Close

    Olaf

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    where do i put this code sir?

  14. #14
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Open Different Data Source Name

    Quote Originally Posted by Cronqvist View Post
    where do i put this code sir?
    As I said: "...Before you try to re-apply the next Connection-String..."

    I'd think that indicates a position above your line which starts with:
    FindCon.ConnectionString = ...

    Olaf

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    this is the cause of a student who didn't take up seriously the subject prola3 and has lots of absent -.-
    i got an idea about simple inventory only.and i search for some codes and new ideas
    those ideas kinda hard to analyze and turn into a debug huhu
    so that is why a simple code from you guys i can't understand where to put it

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    help please

  17. #17
    Member
    Join Date
    Feb 2014
    Location
    In my own world
    Posts
    50

    Re: Open Different Data Source Name

    FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\BSParking.mdb;"
    Instead of the "BSParking.mbd", type the file name you want to open. Make sure it is in the same directory as your project.
    Insert this into the form you want the database to be linked to.
    say the form name is form.1
    Then in your codes section type:
    Form1.Load()
    FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\YOUR FILE NAME HERE.mdb;"

    Tell me if this works

  18. #18

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    Sir, I didn't try your idea. Because i don't want to paste the Database on the same drive where the system is.
    I want is to open any Database anywhere.
    my mainForm has a Listview. In this Listview I can open the 2013.mdb and can open 2014.mdb too this is what I want Sir.


    Current Problem
    Run-time error '3705':
    Operation is not allowed when the object is open.

    Code:
    Private Sub Command1_Click()
        Dim sPathName As String
    
        With CommonDialog1      'Add the Microsoft Common Dialog Control 6.0 (SP6) via the Components dialog box
            .CancelError = True
            .Filter = "Access Database file (*.mdb)|*.mdb"
            .Flags = cdlOFNHideReadOnly
             On Error GoTo 1
            .ShowOpen
             sPathName = .FileName
    1   End With
    
        If LenB(sPathName) Then '<-- If sPathName isn't empty
            FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
        End If
    End Sub
    Highlighted Code
    FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
    Last edited by Cronqvist; Feb 17th, 2014 at 08:43 PM.

  19. #19

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    help please. been waiting for someone who will post into this thread.
    sorry for bumbing this thread. really need to know the answer for this :'(

  20. #20
    Member
    Join Date
    Feb 2014
    Location
    In my own world
    Posts
    50

    Re: Open Different Data Source Name

    What you want is a single listview but say you click a button and it displays one database and when you click other button it displays other database?
    If I am of some help (If that ever happens ) then rate me please . If not then the following sentence should save me: I'm a newbie

  21. #21
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Open Different Data Source Name

    Quote Originally Posted by Cronqvist View Post
    help please. been waiting for someone who will post into this thread.
    The answer was already given - in my post #12.

    Then you asked, where to put this single line of code...
    And that was also answered (in my post #14).

    I don't know what more you need - if you are too lazy to read the replies you were given carefully,
    then you shouldn't wonder why none of the regulars here wants to invest any additional time into
    answering even more questions of yours...


    Olaf

  22. #22

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    dude but i have a problem, look
    http://www.vbforums.com/showthread.p...=1#post4619051
    that is on Post #18

  23. #23
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Open Different Data Source Name

    Quote Originally Posted by Cronqvist View Post
    dude but i have a problem, look
    http://www.vbforums.com/showthread.p...=1#post4619051
    that is on Post #18
    In post #18 you state the following problem:
    Run-time error '3705':
    Operation is not allowed when the object is open.
    And if something is not allowed, when "something else" is still open,
    what about when you try to close it then (before you try the line of code
    which is causing the error).

    I can only repeat - that was answered already in post #12 - and after that
    I was even stating, where you should put that line-of-code in post #14.

    Olaf

  24. #24

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    omg im soo sorry im too stupid
    i really dont get it sir guys i cant understand
    guide please huhu

  25. #25
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Open Different Data Source Name

    Quote Originally Posted by Cronqvist View Post
    omg im soo sorry im too stupid
    i really dont get it sir guys i cant understand
    guide please huhu
    That doesn't help much - I was not trying to intimidate you "into cowering in the corner" -
    we were all Newbies at some point...

    But what one can expect from a Newbie is, that you read the posts 12 and 14 again
    (try the line I've posted there out in your own code) - and then ask concrete questions
    in case something was not working or unclear (with citations of the sentences, "where I lost you").

    Simply stating that you didn't understood what I wrote is not enough - I cannot
    guess here *what* (concretely) you didn't understand - please be more specific -
    and post the attempts you already undertook on your end as code-snippets (with
    appropriate comments, on the lines where you got errors).

    Olaf

  26. #26

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    Modules Connection Code
    Code:
    Public FindCon As ADODB.Connection
    Public FindRs As ADODB.Recordset
    Public Sub openCon()
    Set FindCon = New ADODB.Connection
    FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "/2014.mdb;"
    FindCon.Open
    If FindCon.State = adStateClosed Then
    FindCon.Close
    End If
    End Sub
    Public Sub executeQuery(ByVal sql As String)
    On Error GoTo errorcon
    Set FindCon = New ADODB.Connection
    If FindCon.State = adStateOpen Then
    FindCon.Close
    End If
    openCon
    FindCon.Execute sql, adOpenDynamic, adCmdText
    Exit Sub:
    errorcon:
    MsgBox Error.Description, vbCritical, "Error"
    Set FindCon = Nothing
    End
    End Sub
    Public Sub FindRecordset(ByVal query As String)
    On Error GoTo errorrs
    Set FindRs = New ADODB.Recordset
    If FindRs.State = adStateOpen Then
    FindRs.Close
    End If
    openCon
    FindRs.Open query, FindCon, adOpenDynamic, adLockOptimistic
    Exit Sub:
    errorrs:
    MsgBox Err.Description, vbCritical, "Error"
    Set FindRs = Nothing
    End
    End Sub
    Public Sub RefreshListview()
    Dim Itmx As ListItem
    FindRecordset "Select * From Finder Order By Owner"
    EmployeeMain.ListView.ListItems.Clear
    While Not FindRs.EOF = True
    Set Itmx = EmployeeMain.ListView.ListItems.Add(, , FindRs.Fields("Owner"))
    Itmx.SubItems(1) = FindRs.Fields("EmployeeNumber")
    Itmx.SubItems(2) = FindRs.Fields("IDCount")
    Itmx.SubItems(3) = FindRs.Fields("IDStatus")
    Itmx.SubItems(4) = FindRs.Fields("Department")
    Itmx.SubItems(5) = FindRs.Fields("Acquiredby")
    Itmx.SubItems(6) = FindRs.Fields("IssueDate")
    Itmx.SubItems(7) = FindRs.Fields("ReleaseDate")
    FindRs.MoveNext
    Wend
    End Sub
    Main Form
    Code:
    Private Sub cmdOpenClose_Click()
    Dim sPathName As String
    With CommonDialog1
    .CancelError = True
    .Filter = "Access Database File (*.mdb)|*.mdb"
    .Flags = cdlOFNHideReadOnly
    On Error GoTo 1
    .ShowOpen
    sPathName = .FileName
    1 End With
    If LenB(sPathName) Then
    End If
    FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
    FindCon.Close
    End Sub
    Error
    Code:
    Run-time error '3705': Operation is not allowed when object is open
    Screenshot of Error

    this error code is inside of the Main Form

  27. #27
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Question Re: Open Different Data Source Name

    why you have written the following sub in the module .and opening other connection in cmdopenclose_click event . i am persure following code
    is the cause of this issue .
    Code:
    Public Sub openCon()
    Set FindCon = New ADODB.Connection
    FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "/2014.mdb;"
    FindCon.Open
    If FindCon.State = adStateClosed Then
    FindCon.Close
    End If
    End Sub

  28. #28

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    Sir firoz.raj, you want me to remove that Code from the Module?

    I did remove and i get an error.
    here
    ,


  29. #29
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Open Different Data Source Name

    Quote Originally Posted by Cronqvist View Post
    Screenshot of Error
    Generally, you will fare better when you'd use some kind of indentation on your Code-Snippets -

    With indentation it would become perhaps more obvious, that you introduced another error now
    (in the LenB(sPathName) check)... aside from that, I've now put the line from my post #12
    directly above the line which starts with: "FindCon.ConnectionString =" (as stated in post #14)

    Code:
       If LenB(sPathName) Then
     
          If FindCon.State <> adStateClosed Then FindCon.Close '<- the line from post #12
    
          FindCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPathName & ";"
          FindCon.Open
       End If
    Aside from that, your helper-code-module is not a bad idea - but if you introduce a globally available
    OpenCon-function, then why don't you make any use of it?

    That means, that the above code (which now would work), should be replaced by your new
    OpenCon-Function which sits in your helper-module...

    Your Helper-Module could look like this one here:

    Into a Module:
    Code:
    Option Explicit
     
    Public Con As ADODB.Connection
     
    Public Sub CloseCon()
      If Con Is Nothing Then Set Con = New ADODB.Connection
      If Not Con.State = adStateClosed Then Con.Close
    End Sub
    
    Public Sub OpenCon(mdbFileName As String)
      'since Con is a public Var (and DB-Files might change), we always try to close it first
      CloseCon
      
      'since we ensured above, that Con is "closed and clean" we can change to a different DB-File now...
      Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFileName
      Con.CursorLocation = adUseClient
      Con.Open '... and finally open the connection on the new File
    End Sub
    
    Public Function GetRs(Query As String) As ADODB.Recordset
      Set GetRs = New ADODB.Recordset
      GetRs.Open Query, Con, adOpenStatic, adLockOptimistic
    End Function
    
    Public Sub RefreshListview(LV As ListView, Rs As ADODB.Recordset)
      LV.ListItems.Clear
        Do Until Rs.EOF
          With LV.ListItems.Add(, , Rs("Owner"))
            .SubItems(1) = Rs("EmployeeNumber")
            .SubItems(2) = Rs("IDCount")
            .SubItems(3) = Rs("IDStatus")
            .SubItems(4) = Rs("Department")
            .SubItems(5) = Rs("Acquiredby")
            .SubItems(6) = Rs("IssueDate")
            .SubItems(7) = Rs("ReleaseDate")
          End With
          Rs.MoveNext
        Loop
      Rs.Close
    End Sub
    And with the help of the above Module, the Form-Code could be cleaned up and look more like that:


    Into an empty Test-Form (Project needs a reference to ADO and a ListView1 on top of it)
    Code:
    Option Explicit
    
    Private Sub Form_Load()
    Dim i As Long, DBNames(0 To 1) As String
      'ensure correct settings on a virginal ListView
      ListView1.View = lvwReport
      For i = 0 To 7: ListView1.ColumnHeaders.Add , , "Hdr " & i: Next
      
      'ensure two Temp-MDB-Paths within a String-Array
      DBNames(0) = Environ("Temp") & "\test0.mdb"
      DBNames(1) = Environ("Temp") & "\test1.mdb"
      
      'just two small test-DBs are created (from the array-members, within the Temp-Folder)
      MakeDummyDB DBNames(0)
      MakeDummyDB DBNames(1)
      
      'now we simulate opening (and using) our two different MDB-Files
      OpenCon DBNames(0)     'starting with the one at index 0
      RefreshListview ListView1, GetRs("Select * From Finder Order By Owner")
      
      OpenCon DBNames(1)     'and now switching over to the File at index 1
      RefreshListview ListView1, GetRs("Select * From Finder Order By Owner")
    End Sub
     
    Private Sub MakeDummyDB(mdbFileName As String)
      On Error Resume Next: Kill mdbFileName: On Error GoTo 0
      CreateObject("ADOX.Catalog").Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFileName
      
      OpenCon mdbFileName
      Con.Execute "Create Table Finder(Owner Text, EmployeeNumber Text, IDCount Text, IDStatus Text, Department Text, Acquiredby Text, IssueDate Text, ReleaseDate Text)"
      With GetRs("Select * from Finder Where 0")
        Dim i&, j&
        For i = 0 To 9
          .AddNew
          For j = 0 To 7: .Fields(j) = "Text " & i & "_" & j: Next
        Next
        .UpdateBatch
      End With
    End Sub
    
    Private Sub Form_Unload(Cancel As Integer)
      CloseCon
    End Sub
    I leave it to you, to incorporate and apply the Functions in the Module-Code I've posted, into your own Form.

    Olaf

  30. #30

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    im really stupid. im trying to install all the codes information you give to me.
    i do not know if i got it right or i put them in the right location or with the right brain.
    didn't work. got error: argument not optional

    i did upload the project to mediafire sir
    http://www.mediafire.com/download/sg...nqvist+VB6.rar

  31. #31

    Thread Starter
    Addicted Member
    Join Date
    Dec 2013
    Posts
    166

    Re: Open Different Data Source Name

    help please

  32. #32
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Open Different Data Source Name

    I've had only the time, to adapt the code in your small Form1 to the new *.bas-Module-Content:

    Form1-code:

    Code:
    Option Explicit
     
    Private Sub Form_Load()
        OpenCon App.Path & "\2014.mdb"
        RefreshListview ListView, GetRs("Select " & LVFldList & " From Finder Order By Owner")
    End Sub
    
    Private Sub Form_Resize()
      ListView.Move 0, 400, ScaleWidth, IIf(ScaleHeight - 400 > 0, ScaleHeight - 400, 1)
    End Sub
    
    Private Sub cmdOpenClose_Click()
        With CommonDialog1
            .Filter = "Access Database file (*.mdb)|*.mdb"
            .Flags = cdlOFNHideReadOnly
            .ShowOpen
            If Len(.FileName) = 0 Then Exit Sub
            
            OpenCon .FileName
            RefreshListview ListView, GetRs("Select " & LVFldList & " From Finder Order By Owner")
        End With
    End Sub
    And the new, updated Code which should replace the old one in your *.bas-Module:
    (now containing a generic ListView-Fill-routine, which should work with any Recordset-
    Content (incl. AutoSizing of the ColumnWidth etc.).


    Code:
    Option Explicit
    
    Public Const LVFldList$ = "Owner As [NAME], EmployeeNumber As [EMPLOYEE NO], IDCount As [NO OF ID], " & _
                              "IDStatus As [Status], Department As [DEPARTMENT/OFFICE], Acquiredby As [ACQUIRED BY], " & _
                              "IssueDate As [DATE ISSUED], ReleaseDate As [DATE RELEASED]"
    
    Declare Function SendMessageA& Lib "user32" (ByVal hWnd&, ByVal wMsg&, ByVal wParam&, lParam As Any)
    
    Public Con As ADODB.Connection
     
    Public Sub CloseCon()
      If Con Is Nothing Then Set Con = New ADODB.Connection
      If Not Con.State = adStateClosed Then Con.Close
    End Sub
    
    Public Sub OpenCon(mdbFileName As String)
      CloseCon 'since Con is a public Var (and DB-Files might change), we always try to close it first
      
      'since we ensured above, that Con is "closed and clean", we can change to a different DB-File now...
      Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFileName '... and finally open the Con on the new File
      Con.CursorLocation = adUseClient
    End Sub
    
    Public Function GetRs(Query As String) As ADODB.Recordset
      Set GetRs = New ADODB.Recordset
      GetRs.Open Query, Con, adOpenStatic, adLockOptimistic
    End Function
    
    Public Sub RefreshListview(LV As ListView, Rs As ADODB.Recordset) 'works generically
    Dim i As Long
      LV.View = lvwReport
      LV.FullRowSelect = True
      LV.ListItems.Clear
      LV.ColumnHeaders.Clear
      
      'transport the FieldNames into the Header
      For i = 0 To Rs.Fields.Count - 1
        LV.ColumnHeaders.Add , , Rs(i).Name
      Next
    
      Do Until Rs.EOF
        With LV.ListItems.Add(, , Rs(0))
          For i = 1 To Rs.Fields.Count - 1: .SubItems(i) = Rs(i): Next
        End With
        Rs.MoveNext
      Loop
      Rs.Close
      
      'ensure an AutoSizing of the LV-Columns
      For i = 0 To Rs.Fields.Count - 1: SendMessageA LV.hWnd, &H101E, i, ByVal -2: Next
      If LV.ListItems.Count Then LV.ListItems(2).Selected = True ': LV.ListItems(1).Selected = True
    End Sub
    Hope that helps.

    Of course your other Forms (which still want to use functions from the other Module-Code)
    will then complain when you start-up your project - so it's perhaps better you make a copy
    of your entire project-folder - and then remove the other forms, until only Form1 and the
    EmployeemdlCon-Module remains.

    Then work your way slowly back, introducing one of your just removed forms after the other,
    adapting their code to the new capabilities (especially the new GetRs-Call, which is quite
    powerful when used smartly).

    Just two more things which came to my mind, whilst looking over your project.
    Try to remove these huge Image-Backgrounds (HD-resoultion, 1920x1080) from your Forms
    Picture-Property - a smaller image of e.g. 1280x720 would be fully enough - and then
    apply those BackGrounds on Form_Load directly with one line of code (per LoadPicture)
    dynamically - this would reduce the size of your executable (which is currently about 17MB)
    significantly - just keep the BackGround-image as a JPG-resource (1280x720) in a SubFolder
    below your App.Path and load it from there.

    The other thing is, that two of your forms look nearly identically (the add-form and the update-form).
    It's the same set of Controls which visualize your current Recordset-Fields - and thus you could
    encapsulate these Controls within a UserControl - that's what these things are useful for -
    this way you would have to place and write the basically same code only once...


    Olaf

Posting Permissions

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



Click Here to Expand Forum to Full Width