Writing to two tables

[Paul]

I have an application that I am tryign to modify, I never wrote the
original so need help with my modifications.

Currently the app reads and writes to an Access database with one table
'pupils' I would like to keep this table but only ever update the
records in this table, but at the same time when I create a new record
it puts the current data into a different table 'history' and then
allows me to update the data in the 'pupils' table.

I have code for reading and writing to the 'pupils' table and creating a
new record in the 'pupils' table but when I edit is to look at the
'history' table it falls over. Please help, my code is below:

Public Overloads Function GetPupils() As DataSet
Return Me.GetPupils("lastname")
End Function
Public Overloads Function GetPupils(ByVal sortfield As String) As
DataSet
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim ds As New DataSet
Dim sql As String = "select
firstname,lastname,username,formgroup,yearofintake ,idnum from pupils
order by " + sortfield
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
da.Fill(ds, "pupils")
Finally
da.Dispose()
End Try

Return ds
Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Function GetPupil(ByVal id As Integer) As DataSet
'Return a dataset representing a single pupil
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils where idnum = " &
id.ToString
Dim sa As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet

Try
sa.Fill(ds, "pupils")
Finally
sa.Dispose()
End Try
Return ds

Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Function GetNewPupil() As DataSet

'Return a dataset representing a single pupil
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils where idnum = -1"
Dim sa As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet

Try
sa.Fill(ds, "pupils") ' returns an empty dataset but
with the correct structure
Dim dr As DataRow = ds.Tables(0).NewRow ' creates a
new blank row
ds.Tables(0).Rows.Add(dr) ' add the blank row to the
dataset
Finally
sa.Dispose()
End Try
Return ds ' return the dataset containing one new, blank pupil

Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Sub SavePupils(ByVal ds As DataSet)

Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "select firstname,lastname,idnum from
pupils"
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
Dim cb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds, "pupils")
ds.AcceptChanges()
End If
Finally
da.Dispose()
End Try

Finally
conn.Close()
conn.Dispose()
End Try

End Sub
Public Sub SavePupil(ByVal ds As DataSet)
'Update a dataset representing pupils
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils"
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
Dim cb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds, "pupils")
ds.AcceptChanges()
End If
Finally
da.Dispose()
End Try

Finally
conn.Close()
conn.Dispose()
End Try

End Sub

Thanks in Advance.
Paul.

[Jim Underwood]

Paul,
Sorry, but I don't fully understand what you are trying to do, and what is
not working.

Are you having a problem when you save data, or when you query the data? Do
you know which line/function is causing the error?

One thing I would suggest, if possible, is to not use Access at all but use
SQL Server instead. You get much more flexibility in SQL server and, from
my experience, far fewer bugs than in Access. However, if you are stuck
with Access, try to elaborate a little more on your problem and someone here
is sure to find a solution.
"Paul" <pa**********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

I have an application that I am tryign to modify, I never wrote the
original so need help with my modifications.

Currently the app reads and writes to an Access database with one table
'pupils' I would like to keep this table but only ever update the
records in this table, but at the same time when I create a new record
it puts the current data into a different table 'history' and then
allows me to update the data in the 'pupils' table.

I have code for reading and writing to the 'pupils' table and creating a
new record in the 'pupils' table but when I edit is to look at the
'history' table it falls over. Please help, my code is below:

Public Overloads Function GetPupils() As DataSet
Return Me.GetPupils("lastname")
End Function
Public Overloads Function GetPupils(ByVal sortfield As String) As
DataSet
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim ds As New DataSet
Dim sql As String = "select
firstname,lastname,username,formgroup,yearofintake ,idnum from pupils
order by " + sortfield
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
da.Fill(ds, "pupils")
Finally
da.Dispose()
End Try

Return ds
Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Function GetPupil(ByVal id As Integer) As DataSet
'Return a dataset representing a single pupil
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils where idnum = " &
id.ToString
Dim sa As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet

Try
sa.Fill(ds, "pupils")
Finally
sa.Dispose()
End Try
Return ds

Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Function GetNewPupil() As DataSet

'Return a dataset representing a single pupil
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils where idnum = -1"
Dim sa As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet

Try
sa.Fill(ds, "pupils") ' returns an empty dataset but
with the correct structure
Dim dr As DataRow = ds.Tables(0).NewRow ' creates a
new blank row
ds.Tables(0).Rows.Add(dr) ' add the blank row to the
dataset
Finally
sa.Dispose()
End Try
Return ds ' return the dataset containing one new, blank pupil
Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Sub SavePupils(ByVal ds As DataSet)

Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "select firstname,lastname,idnum from
pupils"
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
Dim cb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds, "pupils")
ds.AcceptChanges()
End If
Finally
da.Dispose()
End Try

Finally
conn.Close()
conn.Dispose()
End Try

End Sub
Public Sub SavePupil(ByVal ds As DataSet)
'Update a dataset representing pupils
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils"
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
Dim cb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds, "pupils")
ds.AcceptChanges()
End If
Finally
da.Dispose()
End Try

Finally
conn.Close()
conn.Dispose()
End Try

End Sub

Thanks in Advance.
Paul.

[Paul]

The issue is that at present the app will read write and add records to
only one table.

I would like to modify the app so that the table that it currently reads
writes and add's records to only gets read and updated and no new
records are added.

I would like the app to clear the txt boxes so it can update the record
with new info (which I can do okay), but before it does that the current
data is added to a different table as a new record which keeps all of
the records execpt the current one.

As I only want the users to see the current record, but also be able to
create reports to report on historical data from the history table.

Thanks,
Paul.

Jim Underwood wrote:

Paul,
Sorry, but I don't fully understand what you are trying to do, and what is
not working.

Are you having a problem when you save data, or when you query the data? Do
you know which line/function is causing the error?

One thing I would suggest, if possible, is to not use Access at all but use
SQL Server instead. You get much more flexibility in SQL server and, from
my experience, far fewer bugs than in Access. However, if you are stuck
with Access, try to elaborate a little more on your problem and someone here
is sure to find a solution.
"Paul" <pa**********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

I have an application that I am tryign to modify, I never wrote the
original so need help with my modifications.

Currently the app reads and writes to an Access database with one table
'pupils' I would like to keep this table but only ever update the
records in this table, but at the same time when I create a new record
it puts the current data into a different table 'history' and then
allows me to update the data in the 'pupils' table.

I have code for reading and writing to the 'pupils' table and creating a
new record in the 'pupils' table but when I edit is to look at the
'history' table it falls over. Please help, my code is below:

Public Overloads Function GetPupils() As DataSet
Return Me.GetPupils("lastname")
End Function
Public Overloads Function GetPupils(ByVal sortfield As String) As
DataSet
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim ds As New DataSet
Dim sql As String = "select
firstname,lastname,username,formgroup,yearofinta ke,idnum from pupils
order by " + sortfield
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
da.Fill(ds, "pupils")
Finally
da.Dispose()
End Try

Return ds
Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Function GetPupil(ByVal id As Integer) As DataSet
'Return a dataset representing a single pupil
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils where idnum = " &
id.ToString
Dim sa As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet

Try
sa.Fill(ds, "pupils")
Finally
sa.Dispose()
End Try
Return ds

Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Function GetNewPupil() As DataSet

'Return a dataset representing a single pupil
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils where idnum = -1"
Dim sa As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet

Try
sa.Fill(ds, "pupils") ' returns an empty dataset but
with the correct structure
Dim dr As DataRow = ds.Tables(0).NewRow ' creates a
new blank row
ds.Tables(0).Rows.Add(dr) ' add the blank row to the
dataset
Finally
sa.Dispose()
End Try
Return ds ' return the dataset containing one new, blank

pupil

Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Sub SavePupils(ByVal ds As DataSet)

Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "select firstname,lastname,idnum from
pupils"
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
Dim cb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds, "pupils")
ds.AcceptChanges()
End If
Finally
da.Dispose()
End Try

Finally
conn.Close()
conn.Dispose()
End Try

End Sub
Public Sub SavePupil(ByVal ds As DataSet)
'Update a dataset representing pupils
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils"
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
Dim cb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds, "pupils")
ds.AcceptChanges()
End If
Finally
da.Dispose()
End Try

Finally
conn.Close()
conn.Dispose()
End Try

End Sub

Thanks in Advance.
Paul.

[Jim Underwood]

I think I understand what you are trying to do...

First, don't use the data adapter for updating, since it is tied to one
table and you will be updating two tables.

When it comes tiem to save the data, first query the existing table to
determine if there is existing data that needs to be copied to the history
table. IF there is, then insert a record into the new table from the old,
THEN, after this is complete, update the existing row in the pupils table.
This is where SQL Server and stored procedures/triggers would come in very
handy.

some quick pseudo code to show what I mean....

Dim sql As String = "Select * from pupils where idnum = " & id.ToString
Dim sa As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, conn)
If rows are returned then
execute SQL "insert into history (select * from pupils where idnum = " &
id.ToString & ")"
end if
' This is where you MAY still want to use your data adapter, since you seem
to already have it ready to do the update
execute SQL "update pupils set value1=value1 where idnum = " & id.ToString

Does this help at all?
"Paul" <pa**********@hotmail.com> wrote in message
news:e2**************@tk2msftngp13.phx.gbl...

The issue is that at present the app will read write and add records to
only one table.

I would like to modify the app so that the table that it currently reads
writes and add's records to only gets read and updated and no new
records are added.

I would like the app to clear the txt boxes so it can update the record
with new info (which I can do okay), but before it does that the current
data is added to a different table as a new record which keeps all of
the records execpt the current one.

As I only want the users to see the current record, but also be able to
create reports to report on historical data from the history table.

Thanks,
Paul.

Jim Underwood wrote:

Paul,
Sorry, but I don't fully understand what you are trying to do, and what is not working.

Are you having a problem when you save data, or when you query the data? Do you know which line/function is causing the error?

One thing I would suggest, if possible, is to not use Access at all but use SQL Server instead. You get much more flexibility in SQL server and, from my experience, far fewer bugs than in Access. However, if you are stuck
with Access, try to elaborate a little more on your problem and someone here is sure to find a solution.
"Paul" <pa**********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

I have an application that I am tryign to modify, I never wrote the
original so need help with my modifications.

Currently the app reads and writes to an Access database with one table
'pupils' I would like to keep this table but only ever update the
records in this table, but at the same time when I create a new record
it puts the current data into a different table 'history' and then
allows me to update the data in the 'pupils' table.

I have code for reading and writing to the 'pupils' table and creating a
new record in the 'pupils' table but when I edit is to look at the
'history' table it falls over. Please help, my code is below:

Public Overloads Function GetPupils() As DataSet
Return Me.GetPupils("lastname")
End Function
Public Overloads Function GetPupils(ByVal sortfield As String) As
DataSet
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim ds As New DataSet
Dim sql As String = "select
firstname,lastname,username,formgroup,yearofinta ke,idnum from pupils
order by " + sortfield
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
da.Fill(ds, "pupils")
Finally
da.Dispose()
End Try

Return ds
Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Function GetPupil(ByVal id As Integer) As DataSet
'Return a dataset representing a single pupil
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils where idnum = " &
id.ToString
Dim sa As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet

Try
sa.Fill(ds, "pupils")
Finally
sa.Dispose()
End Try
Return ds

Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Function GetNewPupil() As DataSet

'Return a dataset representing a single pupil
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils where idnum = -1"
Dim sa As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet

Try
sa.Fill(ds, "pupils") ' returns an empty dataset but
with the correct structure
Dim dr As DataRow = ds.Tables(0).NewRow ' creates a
new blank row
ds.Tables(0).Rows.Add(dr) ' add the blank row to the
dataset
Finally
sa.Dispose()
End Try
Return ds ' return the dataset containing one new, blank

pupil

Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Sub SavePupils(ByVal ds As DataSet)

Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "select firstname,lastname,idnum from
pupils"
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
Dim cb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds, "pupils")
ds.AcceptChanges()
End If
Finally
da.Dispose()
End Try

Finally
conn.Close()
conn.Dispose()
End Try

End Sub
Public Sub SavePupil(ByVal ds As DataSet)
'Update a dataset representing pupils
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils"
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
Dim cb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds, "pupils")
ds.AcceptChanges()
End If
Finally
da.Dispose()
End Try

Finally
conn.Close()
conn.Dispose()
End Try

End Sub

Thanks in Advance.
Paul.

[Paul]

Jim,

I will give it a try. thanks very much for your help.

Paul.

Jim Underwood wrote:

I think I understand what you are trying to do...

First, don't use the data adapter for updating, since it is tied to one
table and you will be updating two tables.

When it comes tiem to save the data, first query the existing table to
determine if there is existing data that needs to be copied to the history
table. IF there is, then insert a record into the new table from the old,
THEN, after this is complete, update the existing row in the pupils table.
This is where SQL Server and stored procedures/triggers would come in very
handy.

some quick pseudo code to show what I mean....

Dim sql As String = "Select * from pupils where idnum = " & id.ToString
Dim sa As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, conn)
If rows are returned then
execute SQL "insert into history (select * from pupils where idnum = " &
id.ToString & ")"
end if
' This is where you MAY still want to use your data adapter, since you seem
to already have it ready to do the update
execute SQL "update pupils set value1=value1 where idnum = " & id.ToString

Does this help at all?
"Paul" <pa**********@hotmail.com> wrote in message
news:e2**************@tk2msftngp13.phx.gbl...

The issue is that at present the app will read write and add records to
only one table.

I would like to modify the app so that the table that it currently reads
writes and add's records to only gets read and updated and no new
records are added.

I would like the app to clear the txt boxes so it can update the record
with new info (which I can do okay), but before it does that the current
data is added to a different table as a new record which keeps all of
the records execpt the current one.

As I only want the users to see the current record, but also be able to
create reports to report on historical data from the history table.

Thanks,
Paul.

Jim Underwood wrote:

Paul,
Sorry, but I don't fully understand what you are trying to do, and what
is
not working.

Are you having a problem when you save data, or when you query the data?
Do
you know which line/function is causing the error?

One thing I would suggest, if possible, is to not use Access at all but
use
SQL Server instead. You get much more flexibility in SQL server and,
from
my experience, far fewer bugs than in Access. However, if you are stuck
with Access, try to elaborate a little more on your problem and someone
here
is sure to find a solution.
"Paul" <pa**********@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl. ..
I have an application that I am tryign to modify, I never wrote the
original so need help with my modifications.

Currently the app reads and writes to an Access database with one table
'pupils' I would like to keep this table but only ever update the
records in this table, but at the same time when I create a new record
it puts the current data into a different table 'history' and then
allows me to update the data in the 'pupils' table.

I have code for reading and writing to the 'pupils' table and creating a
new record in the 'pupils' table but when I edit is to look at the
'history' table it falls over. Please help, my code is below:

Public Overloads Function GetPupils() As DataSet
Return Me.GetPupils("lastname")
End Function
Public Overloads Function GetPupils(ByVal sortfield As String) As
DataSet
Dim conn As OleDb.OleDbConnection = GetConnection()
Try
Dim ds As New DataSet
Dim sql As String = "select
firstname,lastname,username,formgroup,yearofin take,idnum from pupils
order by " + sortfield
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
da.Fill(ds, "pupils")
Finally
da.Dispose()
End Try

Return ds
Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Function GetPupil(ByVal id As Integer) As DataSet
'Return a dataset representing a single pupil
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils where idnum = " &
id.ToString
Dim sa As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet

Try
sa.Fill(ds, "pupils")
Finally
sa.Dispose()
End Try
Return ds

Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Function GetNewPupil() As DataSet

'Return a dataset representing a single pupil
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils where idnum = -1"
Dim sa As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)
Dim ds As New DataSet

Try
sa.Fill(ds, "pupils") ' returns an empty dataset but
with the correct structure
Dim dr As DataRow = ds.Tables(0).NewRow ' creates a
new blank row
ds.Tables(0).Rows.Add(dr) ' add the blank row to the
dataset
Finally
sa.Dispose()
End Try
Return ds ' return the dataset containing one new, blank

pupil
Finally
conn.Close()
conn.Dispose()
End Try

End Function
Public Sub SavePupils(ByVal ds As DataSet)

Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "select firstname,lastname,idnum from
pupils"
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
Dim cb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds, "pupils")
ds.AcceptChanges()
End If
Finally
da.Dispose()
End Try

Finally
conn.Close()
conn.Dispose()
End Try

End Sub
Public Sub SavePupil(ByVal ds As DataSet)
'Update a dataset representing pupils
Dim conn As OleDb.OleDbConnection = GetConnection()

Try
Dim sql As String = "Select * from pupils"
Dim da As OleDb.OleDbDataAdapter = New
OleDb.OleDbDataAdapter(sql, conn)

Try
Dim cb As OleDb.OleDbCommandBuilder = New
OleDb.OleDbCommandBuilder(da)
If ds.HasChanges Then
da.Update(ds, "pupils")
ds.AcceptChanges()
End If
Finally
da.Dispose()
End Try

Finally
conn.Close()
conn.Dispose()
End Try

End Sub

Thanks in Advance.
Paul.

[Paul]

Jim,

It looks like there are errors on the SQL statement as I get a Syntax
error when I try to run it in access.

The error I get in VB was on the cmd.ExecutenonQuery() line. The value
of clsnetusername.gIDNum is the value of the IDNum field of the current
record.

Cheers,
Paul.
Jim Underwood wrote:

Is there any more to that error message? Which line is generating the
error? What is the value of clsNetUserName.gIDNum when the command
executes?

Here is a test...

Add
console.wrtieline(sql2)
to your code before executing the SQL.

Then cut and paste this SQL into Access and run it manually. If it succeeds
in access, then the SQL is good, and we can focus on the VB code to
determine the issue.
"Paul" <pa**********@hotmail.com> wrote in message
news:OR**************@tk2msftngp13.phx.gbl...

Jim,

Here is the now code:

Dim conn1 As OleDb.OleDbConnection = New
OleDb.OleDbConnection("Provider=Microsoft.Jet.OL EDB.4.0;" & "Data
Source=" & mDataPath & "\school.mdb")
Dim id As Integer
Dim sql2 As String = "INSERT INTO History (username,

lastname,firstname,formgroup,yearofintake,dateofwr iting,iamgoodat_1,iamgooda
t_2,iamgoodat_3,dobetter_1,dobetter_2,dobetter_3,h owcandobetter_1,howcandobe
tter_2,howcandobetter_3,target_1,target_2,target_3 ,targetdate_1,targetdate_2
,targetdate_3,subjecttarget,reviewoftarget1,review oftarget2,reviewoftarget3,
idnum)

(select username,

lastname,firstname,formgroup,yearofintake,dateofwr iting,iamgoodat_1,iamgooda
t_2,iamgoodat_3,dobetter_1,dobetter_2,dobetter_3,h owcandobetter_1,howcandobe
tter_2,howcandobetter_3,target_1,target_2,target_3 ,targetdate_1,targetdate_2
,targetdate_3,subjecttarget,reviewoftarget1,review oftarget2,reviewoftarget3,
idnum

from pupils where idnum = " & clsNetUserName.gIDNum & ")"
Dim cmd As New OleDb.OleDbCommand

conn1.Open()
cmd.CommandType = CommandType.Text
cmd.Connection = conn1
cmd.CommandText = sql2
cmd.ExecuteNonQuery()

This brings me the error :
An unhandled exception of type 'System.Data.OleDb.OleDbException'
occurred in system.data.dll

Paul.
Jim Underwood wrote:

Paul,
I dont see a connection string in your code, so this may be the
problem..
Try chaing the first line to
Dim conn As New OleDb.OleDbConnection(ConnectionString)

Substituting for ConnectionString, of course.

Also, I think the SQL sample I suggested is problematic. Try
specifying
the field names and see if that fixes it.

Insert INTO History (field1, feild2) (select field1, feild2 from pupils
where idnum = " & clsNetUserName.gIDNum & ")"
"Paul" <pa**********@hotmail.com> wrote in message
news:ue**************@TK2MSFTNGP12.phx.gbl...
Jim,

I have tried this, could you possibly let me know what is wrong with
this code:

Dim conn As OleDb.OleDbConnection = GetConnection()
Dim id As Integer
Dim sql2 As String = "Insert INTO History (select * from pupils
_ where idnum = " & clsNetUserName.gIDNum
Dim cmd As New OleDb.OleDbCommand

conn.Open()
cmd.CommandType = CommandType.Text
cmd.Connection = conn
cmd.CommandText = sql2
cmd.ExecuteNonQuery()

Thanks,
Paul.

[Paul]

Jim,

I got the SQL to run in Access with the following statement which I put
in my code but it still error's on the ExecuteNoonQuery?

Dim conn1 As OleDb.OleDbConnection = GetConnection()
Dim id As Integer
id = clsNetUserName.gIDNum
Dim sql2 As String = "INSERT INTO History SELECT * FROM pupils
WHERE idNum =" & clsNetUserName.gIDNum & ")"
Dim cmd As New OleDb.OleDbCommand

conn1.Open()
cmd.CommandType = CommandType.Text
cmd.Connection = conn1
cmd.CommandText = sql2
cmd.ExecuteNonQuery()

Any ideas?

Paul.
Jim Underwood wrote:

Is there any more to that error message? Which line is generating the
error? What is the value of clsNetUserName.gIDNum when the command
executes?

Here is a test...

Add
console.wrtieline(sql2)
to your code before executing the SQL.

Then cut and paste this SQL into Access and run it manually. If it succeeds
in access, then the SQL is good, and we can focus on the VB code to
determine the issue.

[Jim Underwood]

Paul...

I think you have an extraneous ")" at the end of your SQL string. Make sure
you keep the
console.wrtieline(sql2)
in your code until you get the sql to execute successfully. Outputting your
SQL and manually running it in your database tool is invaluable for
troubleshooting.
"Paul" <pa**********@hotmail.com> wrote in message
news:%2***************@TK2MSFTNGP15.phx.gbl...

Jim,

I got the SQL to run in Access with the following statement which I put
in my code but it still error's on the ExecuteNoonQuery?

Dim conn1 As OleDb.OleDbConnection = GetConnection()
Dim id As Integer
id = clsNetUserName.gIDNum
Dim sql2 As String = "INSERT INTO History SELECT * FROM pupils
WHERE idNum =" & clsNetUserName.gIDNum & ")"
Dim cmd As New OleDb.OleDbCommand

conn1.Open()
cmd.CommandType = CommandType.Text
cmd.Connection = conn1
cmd.CommandText = sql2
cmd.ExecuteNonQuery()

Any ideas?

Paul.
Jim Underwood wrote:

Is there any more to that error message? Which line is generating the
error? What is the value of clsNetUserName.gIDNum when the command
executes?

Here is a test...

Add
console.wrtieline(sql2)
to your code before executing the SQL.

Then cut and paste this SQL into Access and run it manually. If it succeeds in access, then the SQL is good, and we can focus on the VB code to
determine the issue.

[Paul]

Jim,

Thanks for all your help, it was the extra ")" that was causing the problem.

Its finally working, just a few things to tidy up now.

Thanks a million.
Paul.

Jim Underwood wrote:

Paul...

I think you have an extraneous ")" at the end of your SQL string. Make sure
you keep the
console.wrtieline(sql2)
in your code until you get the sql to execute successfully. Outputting your
SQL and manually running it in your database tool is invaluable for
troubleshooting.
"Paul" <pa**********@hotmail.com> wrote in message
news:%2***************@TK2MSFTNGP15.phx.gbl...

Jim,

I got the SQL to run in Access with the following statement which I put
in my code but it still error's on the ExecuteNoonQuery?

Dim conn1 As OleDb.OleDbConnection = GetConnection()
Dim id As Integer
id = clsNetUserName.gIDNum
Dim sql2 As String = "INSERT INTO History SELECT * FROM pupils
WHERE idNum =" & clsNetUserName.gIDNum & ")"
Dim cmd As New OleDb.OleDbCommand

conn1.Open()
cmd.CommandType = CommandType.Text
cmd.Connection = conn1
cmd.CommandText = sql2
cmd.ExecuteNonQuery()

Any ideas?

Paul.
Jim Underwood wrote:

Is there any more to that error message? Which line is generating the
error? What is the value of clsNetUserName.gIDNum when the command
executes?

Here is a test...

Add
console.wrtieline(sql2)
to your code before executing the SQL.

Then cut and paste this SQL into Access and run it manually. If it
succeeds
in access, then the SQL is good, and we can focus on the VB code to
determine the issue.