Automating Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

quartz - 31 Dec 2003 19:39 GMT

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

Reply to this Message

Douglas J. Steele - 31 Dec 2003 22:20 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

> Hello, please help me if you can.
>
[quoted text clipped - 26 lines]
> 'Open a table
> appAccess.DoCmd.OpenTable strTable

Reply to this Message