Linking to Access in VB

[Leon.Kemp]

I have a project that I am working on that will (ultimately) write to 2 Excel Spreadsheets, 3 Access databases, and communicates with 3 other programs (torque monitoring software, a laser rangefinder, and a specialized industrial inkjet printer). I had much of it working in VB6, but then upgraded (for reasons that I can no longer remember) to .NET. I am now having problems communicating to Access. The main form has 35 text boxes on it fo an operator interface, and using a series of datagrids for the interface is not an option, as this would truely confuse the end users

It seems that most of the material in MSDN assumes that you are using SQL server. Very little is written for Access. This has made life miserable for the last 2 weeks. I should also mention that I am primarily a Network Administrator, but have done some VB programming on the side

I have tried the following
Updating the database from a datasest object (Q301248). (This is where I am at, at present) When the program is run, no errors are found, data column references are accepted, but the database is not updated. Section of code folllows

(in Declarations
Dim drCurrent As DataRo

Dim tblQAData As DataTabl

(during Form load)
tblQAData = PipeData1.Table s("QAData"

(process for writing to 1 database
' Write QA Data for pipe to databas
drCurrent = tblQAData.NewRo
drCurrent("ID") = "
drCurrent("Work Order") = Text3.Tex
If Text25.Text <> "" The
drCurrent("Tube #") = Text25.Tex
End I
If Text5.Text <> "" The
drCurrent("Heat #") = Text5.Tex
End I
If Text9.Text <> "" The
drCurrent("Conn ector#") = Text9.Tex
End I
drCurrent("Weig ht") = Val(Text7.Text
drCurrent("Leng th") = Val(Text9.Text
drCurrent("Shou lderTorque") = "0.0
drCurrent("Delt aTorque") = "0.0
drCurrent("MaxT orque") = "0.0
drCurrent("Prod uctionDate") = Toda
drCurrent("Prod uctionTime") = TimeOfDa
drCurrent("Oper atorName") = Text27.Tex
drCurrent("Trai lerID") = Text16.Tex
drCurrent("Trai lerLoad#") = Val(TextBox1.Te xt
tblQAData.Rows. Add(drCurrent
Previous attempt was to create 5 datagrids with visible set to "false", each one of which would have updated an appropriate table, but could not find a way to write the data in the text boxes to a datagrid

I forget what else I have tried. Very open to suggestions, very frustrated by the extreme granulation of the help files, in that they show too small of a segment to really understand the overall process

Thanks in advance

Leon

[Bob Boran]

One of the biggest changes from ADO to ADO.net is the disconnected model
that ADO.Net employs. This means that where in ADO, when you added or
altered a row, it wrote to the database. In ADO.Net, when you add or alter a
row, it just writes the data to the DataTable. To get the data actually
written to the database you will need to use a DataAdapter. You probably
used one to fill up the DataTable you are currently using.

I will assume a DataSet named PipeData1 and an OledbDataAdapte r named
PipeDataAdapt
-----------------------
Dim PipeDataAdapt as new OleDbDataAdapte r("select * from mypipetable", cn)
Dim PipeData1 as new DataSet

PipeDataAdapt.F ill(PipeData1)

' all your code here, including the add statement.

' now, to get the data back to the db

Dim cb as new OledbCommandBui lder(PipeDataAd apt)
PipeDataAdapt.U pdate(PipeData1 )
------------------------

This is a little rough, but the idea is that you can use an
OleDbCommandBui lder to create the update,insert,d elete statements needed to
get the data back to the table. Then let the DataAdapter do the actual
work. I can give you some more specifics if you can post some code.


"Leon.Kemp" <le*******@hunt ing-intl.com> wrote in message
news:E3******** *************** ***********@mic rosoft.com...

I have a project that I am working on that will (ultimately) write to 2 Excel Spreadsheets, 3 Access databases, and communicates with 3 other
programs (torque monitoring software, a laser rangefinder, and a specialized
industrial inkjet printer). I had much of it working in VB6, but then
upgraded (for reasons that I can no longer remember) to .NET. I am now
having problems communicating to Access. The main form has 35 text boxes on
it fo an operator interface, and using a series of datagrids for the
interface is not an option, as this would truely confuse the end users.
It seems that most of the material in MSDN assumes that you are using SQL server. Very little is written for Access. This has made life miserable
for the last 2 weeks. I should also mention that I am primarily a Network
Administrator, but have done some VB programming on the side.
I have tried the following:
Updating the database from a datasest object (Q301248). (This is where I am at, at present) When the program is run, no errors are found, data
column references are accepted, but the database is not updated. Section of
code folllows:
(in Declarations)
Dim drCurrent As DataRow

Dim tblQAData As DataTable

(during Form load)
tblQAData = PipeData1.Table s("QAData")

(process for writing to 1 database)
' Write QA Data for pipe to database
drCurrent = tblQAData.NewRo w
drCurrent("ID") = ""
drCurrent("Work Order") = Text3.Text
If Text25.Text <> "" Then
drCurrent("Tube #") = Text25.Text
End If
If Text5.Text <> "" Then
drCurrent("Heat #") = Text5.Text
End If
If Text9.Text <> "" Then
drCurrent("Conn ector#") = Text9.Text
End If
drCurrent("Weig ht") = Val(Text7.Text)
drCurrent("Leng th") = Val(Text9.Text)
drCurrent("Shou lderTorque") = "0.0"
drCurrent("Delt aTorque") = "0.0"
drCurrent("MaxT orque") = "0.0"
drCurrent("Prod uctionDate") = Today
drCurrent("Prod uctionTime") = TimeOfDay
drCurrent("Oper atorName") = Text27.Text
drCurrent("Trai lerID") = Text16.Text
drCurrent("Trai lerLoad#") = Val(TextBox1.Te xt)
tblQAData.Rows. Add(drCurrent)

Previous attempt was to create 5 datagrids with visible set to "false", each one of which would have updated an appropriate table, but could not
find a way to write the data in the text boxes to a datagrid.
I forget what else I have tried. Very open to suggestions, very frustrated by the extreme granulation of the help files, in that they show
too small of a segment to really understand the overall process.
Thanks in advance,

Leon

[onedaywhen]

"Bob Boran" <mc*******@hotm ail.com> wrote in message news:<OZ******* ******@tk2msftn gp13.phx.gbl>.. .

One of the biggest changes from ADO to ADO.net is the disconnected model
that ADO.Net employs. This means that where in ADO, when you added or
altered a row, it wrote to the database.

Not so. I've always used ADO classic in a disconnected fashion and
only 'write to the database' when the connection has been
re-established e.g.

' Open connection
Connect True

' Set recordset properties
With m_rsMain

' Client-side cursor crucial for disconnected recordset
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOpti mistic
.Source = strSql
.ActiveConnecti on = m_oConn
.Open
.ActiveConnecti on = Nothing ' << disconnect
End With

' Close connection
Connect False

' Do something with disconnected recordset
...

--

[Leon.Kemp]

Bob

Thanks, that did it. (Although I did not have to use the Dim cb as new OledbCommandBui lder(PipeDataAd apt)) line, as OledbCommandBui lder was not recognized

Very much appreciated

Leon Kem

[Merlin]

Hi,

Do you know you can reference ADODB instead and all the pain goes away!

Merlin
"Leon.Kemp" <le*******@hunt ing-intl.com> wrote in message
news:E3******** *************** ***********@mic rosoft.com...

I have a project that I am working on that will (ultimately) write to 2 Excel Spreadsheets, 3 Access databases, and communicates with 3 other
programs (torque monitoring software, a laser rangefinder, and a specialized
industrial inkjet printer). I had much of it working in VB6, but then
upgraded (for reasons that I can no longer remember) to .NET. I am now
having problems communicating to Access. The main form has 35 text boxes on
it fo an operator interface, and using a series of datagrids for the
interface is not an option, as this would truely confuse the end users.
It seems that most of the material in MSDN assumes that you are using SQL server. Very little is written for Access. This has made life miserable
for the last 2 weeks. I should also mention that I am primarily a Network
Administrator, but have done some VB programming on the side.
I have tried the following:
Updating the database from a datasest object (Q301248). (This is where I am at, at present) When the program is run, no errors are found, data
column references are accepted, but the database is not updated. Section of
code folllows:
(in Declarations)
Dim drCurrent As DataRow

Dim tblQAData As DataTable

(during Form load)
tblQAData = PipeData1.Table s("QAData")

(process for writing to 1 database)
' Write QA Data for pipe to database
drCurrent = tblQAData.NewRo w
drCurrent("ID") = ""
drCurrent("Work Order") = Text3.Text
If Text25.Text <> "" Then
drCurrent("Tube #") = Text25.Text
End If
If Text5.Text <> "" Then
drCurrent("Heat #") = Text5.Text
End If
If Text9.Text <> "" Then
drCurrent("Conn ector#") = Text9.Text
End If
drCurrent("Weig ht") = Val(Text7.Text)
drCurrent("Leng th") = Val(Text9.Text)
drCurrent("Shou lderTorque") = "0.0"
drCurrent("Delt aTorque") = "0.0"
drCurrent("MaxT orque") = "0.0"
drCurrent("Prod uctionDate") = Today
drCurrent("Prod uctionTime") = TimeOfDay
drCurrent("Oper atorName") = Text27.Text
drCurrent("Trai lerID") = Text16.Text
drCurrent("Trai lerLoad#") = Val(TextBox1.Te xt)
tblQAData.Rows. Add(drCurrent)

Previous attempt was to create 5 datagrids with visible set to "false", each one of which would have updated an appropriate table, but could not
find a way to write the data in the text boxes to a datagrid.
I forget what else I have tried. Very open to suggestions, very frustrated by the extreme granulation of the help files, in that they show
too small of a segment to really understand the overall process.
Thanks in advance,

Leon