Automating Access

[quartz]

Hello, please help me if you can.

I am automating Access from Excel. Primarily, I need to
switch data entry "Required" to "No" for various fields
after the table structure is in place (therefore ADO is
not possible).

Can someone please supply me with the automation code I
need to accomplish this task? Thanks very much in advance.

My code so far appears below, but all I can do so far is
open the app.

Dim appAccess As Access.Application
Dim strFullName As String
Dim strTable As String

strTable = "TABLE_NAME"
strFullName = "FULL_NAME"

'Create a new instance of Access
Set appAccess = CreateObject("Access.Application")

'Open the DB and make it visible
appAccess.OpenCurrentDatabase strFullName
appAccess.Visible = True
appAccess.UserControl = True

'Open a table
appAccess.DoCmd.OpenTable strTable

 

[Douglas J. Steele]

If you're trying to change the metadata of the table, you don't open the
table. Rather, you use the TableDef object, and Fields collection of that
object. I don't believe you even need the Access.Application object, so long
as you've set a reference to DAO in your Excel workbook.

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim strFullName As String
Dim strTable As String
Dim strField As String

strTable = "TABLE_NAME"
strField = "FIELD_NAME"
strFullName = "FULL_NAME"

Set dbCurr = OpenDatabase(strFullName)
Set tdfCurr = dbCurr.TableDefs(strTable)
tdfCurr.Fields(strField).Required = False

Set tdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing