Question Repopulate DataGridView after connecting to a new .mdb

G

GoodJuJu

Member
Joined
Apr 26, 2010
Messages
24
Location
England
Programming Experience
5-10
Hi there,

I wonder if somebody could please point me in the right direction.

I have a form which has a DataGridView on it. The DataGridView is populated with data from an
MS access database on 'form load' with no problems.

I have another form (frmSettings) which allows a user to modify various settings.
One option they have is to browse to a different MS Access database (*.mdb).

Part of this works fine... the new database name is held, what I can't seem to get it to do though,
is to refresh the DataGridView with the data from the new .mdb file on the frmMain when I click
on my 'Save & Exit' button on my frmSettings.

If I close and re-open the application, it remembers the name of the other .mdb I selected through
my settings form and populates the DataGridView with the data from the new .mdb.

I can only imagine that there is a problem with the method I am using to try and re-populate /
refresh the DataGridView.

The name of the table in the Access database is 'tblresults'

VB.NET: 
strDBAseName = "C:\Temp\NewDBase.mdb"
        If System.IO.File.Exists(strDBAseName) = True Then 'This is true, works fine, see line '15 - 16

        '=======================================================================
        '== First thing I do is set DataSource for my DataGridView to nothing ==
        '== and refresh, no problems here, it clears the DataGrid View as     ==
        '== expected.  Do I need to even use this step though?                ==
        '=======================================================================
            frmTDRG.DataGridView1.DataSource = Nothing
            frmTDRG.DataGridView1.Refresh()
        '=======================================================================
        '== Next thing I do is reset my Database connection string to the new ==
        '== MS Access Database. This appears to be working fine               ==
        '=======================================================================
'15
            My.MySettings.Default("DBASE_ConnectionConnectionString") = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Chr(34) & strDBAseName & Chr(34) & "")
        '=======================================================================
        '== I then reset the DataGridView's DataSource, which I believe should==
        '== be the new MS Access Database.  Does this look OK?                ==
        '=======================================================================
            frmTDRG.DataGridView1.DataSource = frmTDRG._DBASE_ConnectionDataSet.tblResults
        '=======================================================================
        '== I then 'fill' the DataSet with the results from the new database  ==
        '== or at least this is what I am trying to do.  I think this may be  ==
        '== where it is not working, as my refresh fills the DataGridView     ==
        '== with the results from the original .mdb file                      ==
        '=======================================================================
            TDRG.frmTDRG.TblResultsTableAdapter.Fill(TDRG.frmTDRG._DBASE_ConnectionDataSet.tblResults)
            frmTDRG.DataGridView1.Refresh()

        End If

I have explained the issue as well as I can, any help that anybody can offer is much appreciated.

ADDED... 13/05/2010 14:03 GMT

I have hunted around a bit and I think the question I should really be asking is......

I wish to empty an existing DataSet (_DBASE_ConnectionDataSet) and then fill the emptied DataSet with data from my newly attached .mdb
 
Last edited:
Sort by date Sort by votes
I have got it to work, although I am sure there are more efficient ways...

VB.NET: 
strDBAseName = "C:\Temp\NewDBase.mdb"

If System.IO.File.Exists(strDBAseName) = True Then
'=======================================================================
'== Set DataSource DataGridView to nothing and Refresh                ==
'=======================================================================
    frmTDRG.DataGridView1.DataSource = Nothing
    frmTDRG.DataGridView1.Refresh()
'=======================================================================
'== Clear existing DataSet                                            ==
'=======================================================================
    frmTDRG._DBASE_ConnectionDataSet.Clear()
'=======================================================================
'== Reset Database connection string to the new MS Access DBase       ==
'=======================================================================
    My.MySettings.Default("DBASE_ConnectionConnectionString") = _
    ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Chr(34) & strDBAseName & Chr(34) & "")
'=======================================================================
'== ******************************************************************==
'== THIS IS THE NEW PART                                              ==
'== ******************************************************************==
'== Define SQL String                                                 ==
'=======================================================================
    Dim strSQL As String = "SELECT TAG_NUMBER, TYPE FROM tblResults"
'=======================================================================
'== Define a new TableAdapter, not sure I need to do this, but can't  ==
'== get it to work if I try to use one from My.Mysettings             ==
'=======================================================================
    Dim da As New OleDb.OleDbDataAdapter(strSQL, My.MySettings.Default.DBASE_ConnectionConnectionString)
'=======================================================================
'== Fill the DataSet with the data fro mthe new database              ==
'=======================================================================
    da.Fill(frmTDRG._DBASE_ConnectionDataSet.tblResults)
'=======================================================================
'== ******************************************************************==
'== NEW PART ENDS                                                     ==
'== ******************************************************************==
'=======================================================================
'== Reset the DataGridView DataSource and refresh - voila!!!          ==
'=======================================================================
    frmTDRG.DataGridView1.DataSource = frmTDRG._DBASE_ConnectionDataSet.tblResults
    frmTDRG.DataGridView1.Refresh()

End If

I hope this helps somebody.

Many thanks to anybody who took their time to look for me.
 
Upvote 0 Downvote
I was going to say:

When you construct a new tableadapter, it is at THAT point that the database commands inside it retrieve the new connection string from the settings (actually its a small lie, but it is close enough)

So to change database:

Set the new connection string
Remake your tableadapter (Me.myTableAdapter = New MyTableAdapter)

Use it as normal

i.e.
VB.NET: 
strDBAseName = "C:\Temp\NewDBase.mdb"
        If System.IO.File.Exists(strDBAseName) = True Then 'This is true, works fine, see line '15 - 16

        '=======================================================================
        '== First thing I do is set DataSource for my DataGridView to nothing ==
        '== and refresh, no problems here, it clears the DataGrid View as     ==
        '== expected.  Do I need to even use this step though? [B]NO YOU DONT[/B]               ==
        '=======================================================================
            'frmTDRG.DataGridView1.DataSource = Nothing
            'frmTDRG.DataGridView1.Refresh()
        '=======================================================================
        '== Next thing I do is reset my Database connection string to the new ==
        '== MS Access Database. This appears to be working fine               ==
        '=======================================================================
'15
            My.MySettings.Default("DBASE_ConnectionConnectionString") = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Chr(34) & strDBAseName & Chr(34) & "")
        '=======================================================================
        '== I then reset the DataGridView's DataSource, which I believe should==
        '== be the new MS Access Database.  Does this look OK?  [B]NO, THE CONTAINER THAT HOLDS
THE DATA DOES NOT CARE WHAT THE DB CONNECTION STRING IS[/B]              ==
        '=======================================================================
            'frmTDRG.DataGridView1.DataSource = frmTDRG._DBASE_ConnectionDataSet.tblResults
        '=======================================================================
        '== I then 'fill' the DataSet with the results from the new database  ==
        '== or at least this is what I am trying to do.  I think this may be  ==
        '== where it is not working, as my refresh fills the DataGridView     ==
        '== with the results from the original .mdb file                      ==
        '=======================================================================

   My.MySettings.Default("DBASE_ConnectionConnectionString") = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Chr(34) & strDBAseName & Chr(34) & "")
        '=======================================================================
        '== I then reset the DataGridView's DataSource, which I believe should==
        '== be the new MS Access Database.  Does this look OK?                ==
        '=======================================================================
            frmTDRG.DataGridView1.DataSource = frmTDRG._DBASE_ConnectionDataSet.tblResults
        '=======================================================================
        '== I then 'fill' the DataSet with the results from the new database  ==
        '== or at least this is what I am trying to do.  I think this may be  ==
        '== where it is not working, as my refresh fills the DataGridView     ==
        '== with the results from the original .mdb file [B]BECAUSE YOU DIDNT DO ANYTHING
WITH THE TABLE ADAPTER< IT IS STILL USING THE OLD CONNECTION STIRNG
TO THE OLD DATABASE> MAKE A NEW TABLEADAPTER[/B]                     ==
        '=======================================================================

[B]WHERE blahblah IS THE NAMESPACE OF THE TABLE ADAPTER[/B]
TDRG.frmTDRG.TblResultsTableAdapter = New blahblah.TblResultsTableAdapter

            TDRG.frmTDRG.TblResultsTableAdapter.Fill(TDRG.frmTDRG._DBASE_ConnectionDataSet.tblResults)
            frmTDRG.DataGridView1.Refresh()

        End If
 
Upvote 0 Downvote
Hi Cjard,

Thank you for looking at the problem for me. Yes, that's where I was falling over.... using the old connection string.

Thanks again.
 
Upvote 0 Downvote

Similar threads

H
  • Question
Question Data Export - advice needed
Replies
1
Views
526
jdelano
J
K
  • Question
Table relations issue
Replies
1
Views
1K
jdelano
J
J
  • Question
Can't find where my mistake is
Replies
7
Views
246
jagrenet
J
J
  • Question
Question Printing the contents of a Rich Text Box
Replies
1
Views
117
jmcilhinney
jmcilhinney
M
  • Question
Question I am using VB2019 I want to create a database, I got problems
Replies
1
Views
1K
jmcilhinney
jmcilhinney

Share this page

Latest posts

Back
Top