Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export Single Record to Append to Access Table | Excel Discussion (Misc queries) | |||
Running UPDATE and APPEND queries from Access in Excel | Excel Programming | |||
How do I Append excel data to a Access database table in a excel macro? | Excel Programming | |||
Append Data In Worksheet To Access Table | Excel Programming | |||
Append Data To Access Table | Excel Programming |