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
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.
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. - myCol.Name = Range("Rate_Filing_Fields").Cells(iDataItem, 1).Value
-
myCol.Type = iDataType
-
myCol.DefinedSize = iDataLength
-
myCol.Attributes = adColNullable
-
-
'myCol.Required = "False"
I believe the default Required property setting is false.
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.
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.
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. -
Dim appAccess As Access.Application
-
Dim db As DAO.Database
-
-
Set appAccess = New Access.Application
-
appAccess.UserControl = True
-
-
Set db = appAccess.DBEngine.OpenDatabase("C:\Test\Demo.mdb")
-
db.TableDefs("tblData").Fields("[Certs]").Required = True
-
-
appAccess.Quit acQuitSaveAll
-
Set appAccess = Nothing
-
Set db = Nothing
-
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.
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.
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
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |