By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,660 Members | 1,953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

Help with Access Database and tables

P: n/a
Hi all,

I am trying to convert to vb.net from VB6 and I am haveing a major problem
with connecting to an Access database and adding, updating, deleting data.

This is what I usually use in vb6:

sqlText As String
dbConn As New ADODB.Connection
rsConn As ADODB.Recordset

dbConn.Provider = "Microsoft.Jet.OLEDB.4.0"
dbConn.Properties("Data Source") = App.Path & "\mydatabase.mdb"
dbConn.Open

sqlText = "SELECT * FROM publishers WHERE series=1"

Set rsConn = New ADODB.Recordset
rsConn.Open sqlText
And to read :

while not rsconn.eof
text1=rsconn("field1")
rsconn.movenext
wend
I do not use SQL, since the database is local to the app and since I do not
have SQL.

So please, any assistance/guidance/etc would be greatly appreciated.

George
Mar 22 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Here's a simple data access class you can download that demonstrates how to
access SQL Server databases and/or Access databases.
(www.dxonline.com/files/clsData.vb)

This can be adapted to other databases, assuming the proper drivers and
references are present.

Here's an example of how to get a dataset, make changes to it, and update it
back to the database:
Dim dsList As DataSet = clsData.LoadDataSet("Select * from Customers Where
CustID = 100", gsDriver, gsLocation, sDatabase)

If dsList.Tables(0).Rows.Count <> 0 Then
dsList.Tables(0).Row(0).Item("CustType") = "NOP"
clsData.UpdateDataSet(dsList, "Select * from Customers Where CustID =
100", gsDriver, gsLocation, gsDatabase)
End If

Changing gsDriver between "SQL" and "ACCESS" tells the class what kind of
database to use.
gsLocation is either the path to the Access database, or it's the server
name for the SQL Server.
gsDatabase is the name of the Access database file, or the name of the SQL
database.
--
------------------------------------------------------------------------
George Shubin Custom Software Development
dX Software Systems Database Applications
Ph: 503-981-6806 Fax: 503-982-0120
www.dxonline.com ge****@dxonline.com
------------------------------------------------------------------------

"In politics an absurdity is not a handicap." -- Napoleon Bonaparte

"George" <Ge****@discussions.microsoft.com> wrote in message
news:75**********************************@microsof t.com...
Hi all,

I am trying to convert to vb.net from VB6 and I am haveing a major problem
with connecting to an Access database and adding, updating, deleting data.

This is what I usually use in vb6:

sqlText As String
dbConn As New ADODB.Connection
rsConn As ADODB.Recordset

dbConn.Provider = "Microsoft.Jet.OLEDB.4.0"
dbConn.Properties("Data Source") = App.Path & "\mydatabase.mdb"
dbConn.Open

sqlText = "SELECT * FROM publishers WHERE series=1"

Set rsConn = New ADODB.Recordset
rsConn.Open sqlText
And to read :

while not rsconn.eof
text1=rsconn("field1")
rsconn.movenext
wend
I do not use SQL, since the database is local to the app and since I do
not
have SQL.

So please, any assistance/guidance/etc would be greatly appreciated.

George

Mar 22 '06 #2

P: n/a
George,

I appreciate your help, but when I went to get the class file, I got an error.

I would like to see it if possible.

Thanks,

George

"George Shubin" wrote:
Here's a simple data access class you can download that demonstrates how to
access SQL Server databases and/or Access databases.
(www.dxonline.com/files/clsData.vb)

This can be adapted to other databases, assuming the proper drivers and
references are present.

Here's an example of how to get a dataset, make changes to it, and update it
back to the database:
Dim dsList As DataSet = clsData.LoadDataSet("Select * from Customers Where
CustID = 100", gsDriver, gsLocation, sDatabase)

If dsList.Tables(0).Rows.Count <> 0 Then
dsList.Tables(0).Row(0).Item("CustType") = "NOP"
clsData.UpdateDataSet(dsList, "Select * from Customers Where CustID =
100", gsDriver, gsLocation, gsDatabase)
End If

Changing gsDriver between "SQL" and "ACCESS" tells the class what kind of
database to use.
gsLocation is either the path to the Access database, or it's the server
name for the SQL Server.
gsDatabase is the name of the Access database file, or the name of the SQL
database.
--
------------------------------------------------------------------------
George Shubin Custom Software Development
dX Software Systems Database Applications
Ph: 503-981-6806 Fax: 503-982-0120
www.dxonline.com ge****@dxonline.com
------------------------------------------------------------------------

"In politics an absurdity is not a handicap." -- Napoleon Bonaparte

"George" <Ge****@discussions.microsoft.com> wrote in message
news:75**********************************@microsof t.com...
Hi all,

I am trying to convert to vb.net from VB6 and I am haveing a major problem
with connecting to an Access database and adding, updating, deleting data.

This is what I usually use in vb6:

sqlText As String
dbConn As New ADODB.Connection
rsConn As ADODB.Recordset

dbConn.Provider = "Microsoft.Jet.OLEDB.4.0"
dbConn.Properties("Data Source") = App.Path & "\mydatabase.mdb"
dbConn.Open

sqlText = "SELECT * FROM publishers WHERE series=1"

Set rsConn = New ADODB.Recordset
rsConn.Open sqlText
And to read :

while not rsconn.eof
text1=rsconn("field1")
rsconn.movenext
wend
I do not use SQL, since the database is local to the app and since I do
not
have SQL.

So please, any assistance/guidance/etc would be greatly appreciated.

George


Mar 23 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.