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.Tables("QAData"

(process for writing to 1 database
' Write QA Data for pipe to databas
drCurrent = tblQAData.NewRo
drCurrent("ID") = "
drCurrent("WorkOrder") = 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("Connector#") = Text9.Tex
End I
drCurrent("Weight") = Val(Text7.Text
drCurrent("Length") = Val(Text9.Text
drCurrent("ShoulderTorque") = "0.0
drCurrent("DeltaTorque") = "0.0
drCurrent("MaxTorque") = "0.0
drCurrent("ProductionDate") = Toda
drCurrent("ProductionTime") = TimeOfDa
drCurrent("OperatorName") = Text27.Tex
drCurrent("TrailerID") = Text16.Tex
drCurrent("TrailerLoad#") = Val(TextBox1.Text
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 OledbDataAdapter named
PipeDataAdapt
-----------------------
Dim PipeDataAdapt as new OleDbDataAdapter("select * from mypipetable", cn)
Dim PipeData1 as new DataSet

PipeDataAdapt.Fill(PipeData1)

' all your code here, including the add statement.

' now, to get the data back to the db

Dim cb as new OledbCommandBuilder(PipeDataAdapt)
PipeDataAdapt.Update(PipeData1)
------------------------

This is a little rough, but the idea is that you can use an
OleDbCommandBuilder to create the update,insert,delete 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*******@hunting-intl.com> wrote in message
news:E3**********************************@microsof t.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.Tables("QAData")

(process for writing to 1 database)
' Write QA Data for pipe to database
drCurrent = tblQAData.NewRow
drCurrent("ID") = ""
drCurrent("WorkOrder") = 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("Connector#") = Text9.Text
End If
drCurrent("Weight") = Val(Text7.Text)
drCurrent("Length") = Val(Text9.Text)
drCurrent("ShoulderTorque") = "0.0"
drCurrent("DeltaTorque") = "0.0"
drCurrent("MaxTorque") = "0.0"
drCurrent("ProductionDate") = Today
drCurrent("ProductionTime") = TimeOfDay
drCurrent("OperatorName") = Text27.Text
drCurrent("TrailerID") = Text16.Text
drCurrent("TrailerLoad#") = Val(TextBox1.Text)
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*******@hotmail.com> wrote in message news:<OZ*************@tk2msftngp13.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 = adLockBatchOptimistic
.Source = strSql
.ActiveConnection = m_oConn
.Open
.ActiveConnection = 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 OledbCommandBuilder(PipeDataAdapt)) line, as OledbCommandBuilder 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*******@hunting-intl.com> wrote in message
news:E3**********************************@microsof t.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.Tables("QAData")

(process for writing to 1 database)
' Write QA Data for pipe to database
drCurrent = tblQAData.NewRow
drCurrent("ID") = ""
drCurrent("WorkOrder") = 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("Connector#") = Text9.Text
End If
drCurrent("Weight") = Val(Text7.Text)
drCurrent("Length") = Val(Text9.Text)
drCurrent("ShoulderTorque") = "0.0"
drCurrent("DeltaTorque") = "0.0"
drCurrent("MaxTorque") = "0.0"
drCurrent("ProductionDate") = Today
drCurrent("ProductionTime") = TimeOfDay
drCurrent("OperatorName") = Text27.Text
drCurrent("TrailerID") = Text16.Text
drCurrent("TrailerLoad#") = Val(TextBox1.Text)
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