Update or Append Access Table
 

Hello all I could use some help with Updating or Appending a Table in Access.
I have only done Select Queries so I'm not sure what kind of query i'm
needing.

My Table has about 200,000 lines or so. I would like to set 2 of the Fields
to null or
Nothing. I could go down the Table line by line and backspace the data out
but I have 25 databases that I need to do this with.

Any help would be great

Thanks Mike

Update or Append Access Table
 

This is the Excel Programming NG. Might want to post this in the
Access group.
But, you can run a simple Update Query in the applicable databases to
do what you are wanting. Simply go into the create a new query in
design view. Add the table(s) where you want to perform the update.
From the Query Menu, select Update Query. Add the fields that you
want to update to the field grid. In the Update To field of each
attribute, Enter Null. Then run the query.
Here is an example of what the SQL should look like. This is setting
the fields named Description and SemesterID in the test table to Null.
UPDATE test SET test.Description = Null, test.SemesterID = Null;

Mike wrote:
Hello all I could use some help with Updating or Appending a Table in Access.
I have only done Select Queries so I'm not sure what kind of query i'm
needing.

My Table has about 200,000 lines or so. I would like to set 2 of the Fields
to null or
Nothing. I could go down the Table line by line and backspace the data out
but I have 25 databases that I need to do this with.

Any help would be great

Thanks Mike

Update or Append Access Table
 

I would like to do this in excel with a command button
for the reason I don't want my employees in the Access database

Could someone help me with this? Heres what I have so far

Sub upDateRegSaleTable()
Dim cn As DAO.Connection
Dim strSQL1 As String
strSQL1 = "UPDATE RegSale SET RegSale.SaleTaxID = """ _
& "WHERE (((RegSale.SaleTaxID)""));"
Set cn = OpenDatabase("C:\Ilsa\Data\Ilsa.mdb") 'Error's here #13 Type MisMatch
With cn
..Execute strSQL1, dbFailOnError
..Close
End With
Set cn = Nothing

"JW" wrote:

This is the Excel Programming NG. Might want to post this in the
Access group.
But, you can run a simple Update Query in the applicable databases to
do what you are wanting. Simply go into the create a new query in
design view. Add the table(s) where you want to perform the update.
From the Query Menu, select Update Query. Add the fields that you
want to update to the field grid. In the Update To field of each
attribute, Enter Null. Then run the query.
Here is an example of what the SQL should look like. This is setting
the fields named Description and SemesterID in the test table to Null.
UPDATE test SET test.Description = Null, test.SemesterID = Null;

Mike wrote:
Hello all I could use some help with Updating or Appending a Table in Access.
I have only done Select Queries so I'm not sure what kind of query i'm
needing.

My Table has about 200,000 lines or so. I would like to set 2 of the Fields
to null or
Nothing. I could go down the Table line by line and backspace the data out
but I have 25 databases that I need to do this with.

Any help would be great

Thanks Mike

Update or Append Access Table
 

Sub updateDB()
Dim conn As Object, sql As String
sql = "UPDATE RegSale SET RegSale.SaleTaxID = Null" _
& " WHERE (((RegSale.SaleTaxID) Is Not Null));"
Set conn = CreateObject("ADODB.Connection")
On Error GoTo errHandler
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "C:\Ilsa\Data\Ilsa.mdb"
conn.Execute sql
closer:
On Error Resume Next
conn.Close
Set conn = Nothing
Exit Sub
errHandler:
MsgBox "An error occured" & Chr(10) & _
Err.Description, , Err.Number
Resume closer
End Sub

Mike wrote:
I would like to do this in excel with a command button
for the reason I don't want my employees in the Access database

Could someone help me with this? Heres what I have so far

Sub upDateRegSaleTable()
Dim cn As DAO.Connection
Dim strSQL1 As String
strSQL1 = "UPDATE RegSale SET RegSale.SaleTaxID = """ _
& "WHERE (((RegSale.SaleTaxID)""));"
Set cn = OpenDatabase("C:\Ilsa\Data\Ilsa.mdb") 'Error's here #13 Type MisMatch
With cn
.Execute strSQL1, dbFailOnError
.Close
End With
Set cn = Nothing

"JW" wrote:

This is the Excel Programming NG. Might want to post this in the
Access group.
But, you can run a simple Update Query in the applicable databases to
do what you are wanting. Simply go into the create a new query in
design view. Add the table(s) where you want to perform the update.
From the Query Menu, select Update Query. Add the fields that you
want to update to the field grid. In the Update To field of each
attribute, Enter Null. Then run the query.
Here is an example of what the SQL should look like. This is setting
the fields named Description and SemesterID in the test table to Null.
UPDATE test SET test.Description = Null, test.SemesterID = Null;

Mike wrote:
Hello all I could use some help with Updating or Appending a Table in Access.
I have only done Select Queries so I'm not sure what kind of query i'm
needing.

My Table has about 200,000 lines or so. I would like to set 2 of the Fields
to null or
Nothing. I could go down the Table line by line and backspace the data out
but I have 25 databases that I need to do this with.

Any help would be great

Thanks Mike