473,320 Members | 2,097 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Setting Required Field to No - Excel VBA to Access

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
May 2 '19 #1
11 1425
NeoPa
32,556 Expert Mod 16PB
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.
May 2 '19 #2
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"
May 3 '19 #3
twinnyfo
3,653 Expert Mod 2GB
I believe the default Required property setting is false.
May 3 '19 #4
Rabbit
12,516 Expert Mod 8TB
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.
May 3 '19 #5
NeoPa
32,556 Expert Mod 16PB
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.
May 4 '19 #6
ADezii
8,834 Expert 8TB
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.  
May 6 '19 #7
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.
May 8 '19 #8
Rabbit
12,516 Expert Mod 8TB
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.
May 8 '19 #9
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
May 8 '19 #10
twinnyfo
3,653 Expert Mod 2GB
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.
May 8 '19 #11
Rabbit
12,516 Expert Mod 8TB
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.
May 8 '19 #12

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

Similar topics

3
by: Orchid | last post by:
Hello All, Hope someone can help me on my required field problems. I have a form base on a table for users to input new Employees. There are 4 fields that cannot be Null when entering new...
5
by: Rand | last post by:
I have Required Field Validators in 10 pages, and they all work fine on development, but for 9 out of the same 10 pages, on production, they act as if they aren't even present (it is unlikely they...
2
by: z. f. | last post by:
Hi, i have a field with required field validator. on the client side it's working. when i submit without the required field the page gets back because of not valid, but the message from the...
3
by: Kivak Wolf | last post by:
Hi, Could someone give me a quick review of how to use the required field Validator? I'm completely at a loss, and MSDN does not help one bit. =/ Basically all I want done is to make sure that...
3
by: Joachim | last post by:
Hi I am a beginner in VB.NET, and have a problem with empty field in Access I have transfered a worksheet in Excel to Access table. Some of the cels are empty I use VB.NET program to acces...
7
by: MN | last post by:
I am using a program called IDWorks which is reading and writing to an MS Access format database. The IDWorks program can't adjust the formatting on a displayed field in a way that we need so I...
0
by: dinsdale | last post by:
Wow, this is a really embarassing thing to have to ask about... I have an asp.net page with a multiview control in it. We want to enter person info in a semi - wizard style and I wanted to use...
7
by: kiwipedia | last post by:
Hi all I have a combo-box control which I've set as a required field. It's a date and uses a calendar as coded below. All works fine unless the user deletes a pre-existing value (leaving a...
2
by: sam the lion | last post by:
Hi, I'm currently running Access 2007 on Windows XP Professional. I'm a novice for the most part, and this is my first database. I'm having difficulty figuring out how to do the following: 1....
0
by: Simon | last post by:
Dear reader, How to export a hyperlink field from Access to Excel, so it is also working in Excel. In Excel you have for the Hyperlink to parts: a.. Text To Display
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.