By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,831 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,831 IT Pros & Developers. It's quick & easy.

Setting Required Field to No - Excel VBA to Access

P: 4
hi - i'm working in Excel writing code to send data from an Excel spreadsheet to an Access table. I create the table from Excel. How do I make the "Required" field in Access "No" using VBA code in Excel? I am using Access 2016.

Thanks for your help.

Patty
2 Weeks Ago #1
Share this Question
Share on Google+
11 Replies


NeoPa
Expert Mod 15k+
P: 31,302
Hi Patty.

There's really very little for anyone to go on from your question.

There are many ways to skin a cat. Without knowing how you're approaching this particular issue we can't easily respond. Two approaches come to mind (SQL & Object model) but we don't know if you're using either. Are you using Application Automation or trying to create a specifically Excel-based solution. Without some sort of basic framework we're on a hiding to nothing.

I suggest you post the relevant portion of your code so that we can get an idea of what we're actually dealing with.
2 Weeks Ago #2

P: 4
Hi - sorry for the lack of context. We are trying to set up a table in Access from Excel. We want to control the property "Required" to No or False. This way if no data is available, it's ok that the field is blank. Here is some of the code we have so far. The last step with ' seems to be the code using Access versions before 2016. We are looking for the new syntax.
Expand|Select|Wrap|Line Numbers
  1. myCol.Name = Range("Rate_Filing_Fields").Cells(iDataItem, 1).Value
  2. myCol.Type = iDataType
  3. myCol.DefinedSize = iDataLength
  4. myCol.Attributes = adColNullable
  5.  
  6. 'myCol.Required = "False"
2 Weeks Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,158
I believe the default Required property setting is false.
2 Weeks Ago #4

Rabbit
Expert Mod 10K+
P: 12,335
Why do it in code at all? If the field is not required, just go into the table design view and change the column definition manually.
2 Weeks Ago #5

NeoPa
Expert Mod 15k+
P: 31,302
I'm sorry to say there's still far too little information to work from. How have you created the table in your code? How is [myCol] Dimmed? We have almost nothing in the way of context to work from still.

All that said, one glaring error appears to be that your commented line appears to be trying to set its value to a string ("False") rather than Boolean (False) value.
2 Weeks Ago #6

ADezii
Expert 5K+
P: 8,615
I didn't think that you could, but you can dynamically set the Required Property of a Field in an Access Table by opening the DB using Automation Code from Excel. The following Code (short Version) will set the Required Property of a Field named [Certs] in tblData in the C:\Test\Demo.mdb Database to True. This is all done solely from within Excel. I do agree with Rabbit in that you can simply change the Property manually from within Access. The Code is by no means optimized, I simply wanted to see if this can be done.
Expand|Select|Wrap|Line Numbers
  1. Dim appAccess As Access.Application
  2. Dim db As DAO.Database
  3.  
  4. Set appAccess = New Access.Application
  5.     appAccess.UserControl = True
  6.  
  7. Set db = appAccess.DBEngine.OpenDatabase("C:\Test\Demo.mdb")
  8.     db.TableDefs("tblData").Fields("[Certs]").Required = True
  9.  
  10. appAccess.Quit acQuitSaveAll
  11. Set appAccess = Nothing
  12. Set db = Nothing
  13.  
2 Weeks Ago #7

P: 4
We are setting up the database from Excel using VBA - we have 94 fields that we are setting up in our particular example and want to control the property setting from Excel VBA.
1 Week Ago #8

Rabbit
Expert Mod 10K+
P: 12,335
But does it have to be that way? Why go through the trouble of using code to create and configure a database when you could create the database beforehand and link it to the spreadsheet? Is it always 94 fields? How do you know what the DDL is for the different fields in the Excel table? Is this a one time import or recurring?

If the end goal is just to import excel data into an Access table, there are much easier ways of accomplishing that than messing around with creating and defining tables and fields from VBA code. For example, just link the spreadsheet in Access. Or use the import function. Or if there needs to be some sort of dynamic definition, create an import spec instead.

If we knew what the ultimate goal is instead of the specific solution you've settled on, we might be able to come up with a simpler solution.
1 Week Ago #9

P: 4
Our ultimate goal is to have flexibility in the data that we are downloading from a website - from Excel to Access. Part of the goal is to learn as much as we can about coding, so we are trying to learn a catch all approach when a datafield is blank and don't want to send to Access
1 Week Ago #10

twinnyfo
Expert Mod 2.5K+
P: 3,158
I'm on board with Rabbit. Just link the Spreadsheet, Import it, or create a table with the required fields you need and copy and paste. I've created tables in the past, and I know I can do it, but if there is any way that I can NOT do it, I'll go that route first.
1 Week Ago #11

Rabbit
Expert Mod 10K+
P: 12,335
The built in import process within Access is pretty good at just bringing in the entire spreadsheet without you having to define anything.

You really only need to change it if you need to tighten up the definitions. In which case, you're getting that definition from somewhere. And if you have that definition, it's probably easier to dynamically create an import specification instead.

I understand the motive to "learn as much as we can about coding" but learning this particular set of skills where you're defining and changing table definitions through VBA code is not something I've had to use in my many years of coding. I will usually just use VBA to call the native import function from Access.
1 Week Ago #12

Post your reply

Sign in to post your reply or Sign up for a free account.