Two general Data Query Questions

  • Thread starter Thread starter Mr. B
  • Start date Start date
M

Mr. B

VB.net and MS Access OLE

Two Questions:

First...
+++++

I've used the Wizard to create my OLE Connection, DataAdapter's and DataSet's
to my MS Access data bases. Is there a way to change the path to the Data
Base files at Run time via Code?

.... OR should I repace the Connections with Manual Code? If so, is the
following Correct?

Dim conStr As String = _

"Provider=Microsoft.JET.OLEDB.4.0;datasource=C:\\TSdata\\ExtraServicesData.mdb"

Dim oleStr As String = "SELECT * FROM tblTimeEntry"

' Create connection object
Dim conn As OleDbConnection1 = New OleDbConnection(conStr)

' Create data adapter object
Dim da As OleDbDataAdapter = New OleDbDataAdapter(oleStr, conn)

' Create a dataset object and fill it
Dim oDataSet As New DataSet( )

da.Fill(oDataSet, "tblTimeEntry")

.... am I missing anything here? Cause I have tried it and the FILL line gets
an error :(


Second...
++++++++

If I've a SELECT in my DataAdapter and that has a filter like:
WHERE (User = ?)

Then I run the following.

daUserData.SelectCommand.Parameters(0).Value = UserID
daUserData.Fill(DsUserData.tblTimeEntry)

This work OK... what I'd like to know is IF there is a way to have "UserID" as
a global value instead of a filter string? In other words, I'ld like to use
the above for both filtering my data... and... selecting it all without a
filter. Sort of like using *

Can this be done?

Thanks!

Regards,

Bruce
 
Hi,

Your connection string should look like

Dim conStr As String = _
"Provider=Microsoft.JET.OLEDB.4.0;Data
Source=C:\TSdata\ExtraServicesData.mdb;User ID=Admin;Password="

Rest of code looks fine, but I would suggest to limit selection using WHERE
clause in your SELECT statment. Any reason to select all records?
 
Val Mazur said:
Rest of code looks fine, but I would suggest to limit selection using WHERE
clause in your SELECT statment. Any reason to select all records?
Click to expand...

Ummm... I 'thought' (or am of the Understanding) that you need to do this if
you are going to delete a whole record row. Correct me if I'm wrong. I've
only been working with a DB for about 3 weeks now and have a LOT still to
learn.

But many Thanks for your info. I'll try it out.

For what it's worth, I asked my question because:
1) I've only (so far) used the Wizards to create my Connections, DataSet and
DataAdapters.

2) I want to change my path to my Data Bases easily as when I'm writing my
code, I have a 'copy' on my local drive. Rather than where the real one is.
So this is to make my switching over to the 'real' DB easier.

Regards,

Bruce
 
Hi,

Limiting selection is a good programming practice. You need to limit
selection to reduce network traffic and loading of the database engine.
Doing this you will select only records you really need to work with.
Otherwise you will get performance issues in your application. Limiting of
the selection is not related to the deleting of the records at all
 
Val Mazur said:
Otherwise you will get performance issues in your application. Limiting of
the selection is not related to the deleting of the records at all
Click to expand...

Ah! Okay... thanks for the info. Kind of makes sense. For sure I'd rather
work only with what I need!

Thanks again.

Bruce
 
Back
Top