Is multiple instance of an Access object possible?

Hi,

I hope somebody can give some guide regarding Access db files and VB. I am working on my final year project and have decided to use vb as a user interface for student grouping according by test grade results.

I have managed to get the open file dialog to work while opening the access db file:

Dim myConn As New OleDbConnection

Dim di As DialogResult

di = dlgDataImport.ShowDialog()

If di = Windows.Forms.DialogResult.OK Then

Dim strFile As String = dlgDataImport.FileName

myConn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" & strFile & ";"

myConn.Open()

Dim myCMD As New OleDbCommand

myCMD.CommandText = "Select * FROM Results"

myCMD.Connection = myConn

Dim dssAdapter As New OleDbDataAdapter

dssAdapter.SelectCommand = myCMD

Dim ds As New DataSet

ds.Clear()

dssAdapter.Fill(ds, "Results")

dgvImportData.DataSource = ds

dgvImportData.DataMember = "Results"

End If

This works quite well. Now is there any way of making the file we opened using this coding available to another data gridview form within the same project. I plan to allow say a teacher to fill in a dialog/form which requires the teacher to input a test grade the teacher wants the students to be grouped by. By clicking the button on the form, a new window with the sorting results will appear. The results is based on the file which is already opened initially. Is there any way of doing this or do I need to upgrade to the standard edition

Answer this question

Is multiple instance of an Access object possible?

Brandon Evans
Thank you for repsonding. I seem to fail to mention that I was working with Oledb since I intend to link the vb thing to Access database. I am quite new to vb so my enquiries may be a bit on the obvious side. Anyway, here is some back ground on what I want the form to do:

First, the user will access the database file by clicking the menu item, which code I included in my very first thread. Hence, the main window will display this database file. When the user wants to do the sorting, he will click a menu item, lets call it Grouping and a dialog form will pop-up. This form (lets call it Gform) has textboxes and will take in users input. On clicking the OK button, supposedly another form with datagridview (lets call it GResultForm) will show the already sorted.

Now, I find myself stumped due to variables that are needed are from different classes ie the sorting criteria (input by user in textbox) is in Gform but the result of the sorting is displayed in GResultForm. Is there any way to pass the variable from GForm to GResultForm And the GResultForm's datagridview is a new instance of the datagridview of the main window except for the OledbCommand string which will utilise textbox input from Gform.

Based on this scenario, is there any hint/tips/suggestion on coding this I think I bitten more than I can chew here. All assistance renered are highly appreciated.
Weifen Luo
You can use the same connection to execute another query if you wish. Create another query with an "ORDER BY" clause to do the sorting, load it into the adapter, and fill it to a dataset (perhaps a different one) intended for display.
kagi

I'd like to commend you in your attempt to ask a clear question and I'm still confused from a systems pov.

I don't understand what you mean by this " within the same project. " ... does this mean, the same process

Are you multiple users on multiple nodes
seesyong
James,

Your suggestion is what I was looking for. So far,from reading, I believe that every time we need to instanciate a new object we alway have to do something like

Dim someObject as New someClass

What if we want to get a new dataGridView display of a new query based on the same connection Do we still have to declare a new OleDb connection or can we just pull it from the one thats already established Can this really be done can anybody demonstrate how this can be done
Faisal_Malik

hi

i like this idea , let me think about this and i'll tell you how to do this

best regards

Haitham
Beth1
hi,

Ahmed, the previous code for one form to display data doesn't matter what datalooks like all what you have to do is to change the SQl string,

it uses constractor and property to know more about those things you can read this article

http://www.programmersheaven.com/2/Les_VBNET_4_p1

to know more about data connection you can see those links
hope this helps
Mont Rothstein
Hi again,

went to try out something and I got this error message:

The Microsoft Jet database engine cannot open the file 'C:\MITalong\Project\test'. It is already opened exclusively by another user, or you need permission to view its data."

So I guess getting a new query on an already opened file is not possible by creating a new connection to the file. Is there a programatically correct way of doing this or is it just not allowed by vb.net

Thanks,

Ariff
Srinivas N

hi,

By clicking the button on the form, a new window with the sorting results will appear. The results is based on the file which is already opened initially. Is there any way of doing this

sure there is a way to do so but your question is not clear , i'm guessing here , you can add a property to your form for sqlstring you can start a new instance from your form and pass the sqlstr to the new instance

hope this helps
EBCIDIC
Hi Shakalama,

Thanks for the code. Let me go through it first to see if I can fit it into my app. It seems a bit complicated though, does any of the forum members have any alternative suggestions I would very much like to avoid using threads, trying to work this is already taxing.

Thanks again,

Ariff

amiratish

ok,

here we go, i used the same form to run in many different flavors depend on constractors and changing the sql string , and another form as dialog for user to select which column he want to group by , this code need to be polished a little bit, this code might contain errors because i wrote it in C# and used code translator for VB

form1 which contain datagridview and a button

Namespace WindowsApplication1
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows.Forms

Public Class Form1
Inherits Form

Private ds As DataSet = New DataSet("myds")

'the default constractor for your form that will be called when the form start up
'this constractor will pass a normal select statment to the main constractor
Public Sub New()
Me.New("SELECT date_t, name_t, value_t from datet")

End Sub

'this is the main constractor that you will call when you start any instance of this form
Public Sub New(ByVal SQlString As String)
MyBase.New
InitializeComponent
Dim connStr As String = "provider=Microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\Kakamo\Desktop\dateTesting.mdb"& _
""
Dim conn As OleDbConnection = New OleDbConnection(connStr)
Dim dbcomm As OleDbCommand = New OleDbCommand
Dim reader As OleDbDataReader = Nothing
Try
conn.Open
dbcomm.CommandType = CommandType.Text
dbcomm.CommandText = SQlString
dbcomm.Connection = conn
reader = dbcomm.ExecuteReader
ds.Load(reader, LoadOption.Upsert, New String() {"date"})
Catch ex As InvalidOperationException
MessageBox.Show(ex.Message)
Catch ex As OleDbException
MessageBox.Show(ex.Message)
Finally
reader.Close
conn.Close
End Try
dataGridView1.DataSource = ds
dataGridView1.DataMember = "date"
End Sub

'a button that will be used to open the other form
'and pass for it the table names to be displayed in the combobox
Private Sub btnGroupBy_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim count As Integer = ds.Tables("date").Columns.Count
Dim columns() As String = New String((count) - 1) {}
Dim i As Integer = 0
Do While (i < count)
'get the columns names from the dataset
columns(i) = ds.Tables("date").Columns(i).ColumnName
i = (i + 1)
Loop
'open the form2 which user will select which column he want to group by
'pas for it the columns name
Dim f2 As Form2 = New Form2(columns)
Dim dr As DialogResult = f2.ShowDialog
If (dr = DialogResult.OK) Then
'here the trick
'get from form 2 which index was selected
'resolve this index form the columnsName array
'append it to the sql string
' run a new instance of this form through the main constractor not the default one
Dim groupbyStr As String = ("SELECT name_T, Count(value_T) AS Recordcount FROM dateT GROUP BY " + columns(f2.SelectedIndex))
Dim f1 As Form1 = New Form1(groupbyStr)
f1.ShowDialog
End If
End Sub
End Class
End Namespace

form2 which contains 2 buttons and a combobox

Namespace WindowsApplication1
Imports System
Imports System.Windows.Forms

Public Class Form2
Inherits Form

' property to pass the selected index form the combobox to the caller
Private _selectedindex As Integer

' main constractor to recieve array of strings and to
'fill the combobox with it
Public Sub New(ByVal combooxItems() As String)
MyBase.New
InitializeComponent
'make this form a dialog
button1.DialogResult = DialogResult.OK
button2.DialogResult = DialogResult.Cancel
comboBox1.Items.AddRange(combooxItems)
End Sub

Public ReadOnly Property SelectedIndex As Integer
Get
Return _selectedindex
End Get
End Property

Private Sub comboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
_selectedindex = comboBox1.SelectedIndex
End Sub

Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)
Me.Close
End Sub

Private Sub button2_Click(ByVal sender As Object, ByVal e As EventArgs)
Me.Close
End Sub
End Class
End Namespace

hope this helps

Is multiple instance of an Access object possible?

Answer this question