Use Excel to Modify Access Table?

tiomin

Board Regular
Joined
Nov 27, 2003
Messages
147
I have several hundred tables in Access that I need to fix. Each mdb has the same tables and columns. For each mdb I have a table name ABC with a column ZYZ. I would like a macro to search through each mdb in a directory and change the values in column ZYZ to hold a value of 0 (zero) instead of a null. Can this be done through VB? If so how should I approach this?

On the same topic: if column MMM has a value of 12345678ABC and I would like to retain only the first 8 characters and drop the ABC, how would I do this?

Thanks
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Maybe like this? I've late bound, so you can be in VBA from Word, Outlook, Excel or whatever. HTH, Fazza
Code:
Sub Update_mdb_Files()

  Const strFILE_PATH As String = "M:\DataFiles" 'SET THIS FOR YOUR DIRECTORY
  
  Dim strDB_Name As String

  strDB_Name = Dir(strFILE_PATH & Application.PathSeparator & "*.mdb")
  Do While Not strDB_Name Like vbNullString
    Call Update_Table_ABC(strFILE_PATH & Application.PathSeparator & strDB_Name)
    strDB_Name = Dir()
  Loop

End Sub

Sub Update_Table_ABC(ByVal strDB_FullName As String)

  Dim objConn As Object

  Set objConn = CreateObject("ADODB.Connection")
  With objConn
    .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB_FullName & ";"
    .Execute "UPDATE ABC SET ABC.XYZ = 0 WHERE ABC.XYZ Is Null"
    .Close
  End With
  Set objConn = Nothing

End Sub
 
Upvote 0
PS.

It might be better to post the question in the Access forum. I'm not familiar with using MS Access and, though this seems to work, the posters there will know the best approach. F
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top