Solved

Create table syntax , look up the new table  name

Posted on 2008-10-06
7
359 Views
Last Modified: 2013-11-28
I cant find a refrence for SQL Syntax
I'm tring to create a table in Access through VBA

"ID"
AutoNumber, Primary Key

"Product"
Lookup ID from table 'Cat-Type'
Is Required
Validation  '= 14'

"PartNo"
Char(255)
Is Required
No Duplicates

I have a table:
Cat-Type
ID -int
Category - int
Type - int

The New table name is
[Cat-Type].ID Where
[Cat-Type].[Category] = 1
[Cat-Type].[Type] = 1

If anyone also has a sugestion where to lookup syntax would apreciate also
0
Comment
Question by:CMDAI
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 1

Author Comment

by:CMDAI
ID: 22656622
I Would specify the table name but i docmd dont know how to
return the auto number created after i just called this:

DoCmd.RunSQL(
"INSERT INTO [Cat-Type] (Category,Type)
Values ('" & Me.cmbCategory & "' , '"  &  Me.cmbType &  "') ")
0
 
LVL 85
ID: 22657915
Do you mean that you are trying to create a table with a Lookup column defined on the table? If so, this is a bad idea ... and there is no reason to do so, since the ONLY benefit of doing this is the 2 - 3 seconds you save when creating a form (since Access would then automatically add the field as a combo instead of a textbox).

You will have to use DAO to create your validation rules and such, since those are Jet/Access specific and you cannot do so via straight SQL ... check the CreateTableDef topic in the Help file for full syntax declarations, but basically:

Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim dbs As DAO.Database

Set dbs = Currentdb
Set tdf = dbs.CreateTableDef("YourTableName")
'/FieldType is a NUMERIC value
Set fld = tdf.CreateField("FieldName", FieldType)
'/if this is a AutoNumber
fld.Attributes = fld.Attributes Or dbAutoIncrField
'/if it's required
fld.Required = True
'/if it has a validation rule
fld.ValidationRule="Your Validation Rule
fld.ValidationText = "Your Validation TExt"
'/now append the field
tdf.Fields.Append fld
tdf.Fields.Refresh

'/now add your other fields

'/now append the Table and refresh the collection
dbs.Tabledefs.Append tdf
dbs.Tabledefs.Refresh


0
 
LVL 1

Author Comment

by:CMDAI
ID: 22658067
I tried the code it works very nicely,
can you help me fast forward, the pic i attached shows
2 tables, Im creating [tamplate Description1]
How to set 1to1 rlationship on the new table with this code?

Field.jpg
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:CMDAI
ID: 22658078
Just for comment [products] (Supertype)
new table is (Subtype)
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 22660669
This is really a different question ... basically you'd have to add a Relation to your database. You do this AFTER adding your table/fields of course:

Dim rel As DAO.Relation
Dim fld As DAO.Field

Set rel = CurrentDB.CreateRelation("RelationName", "LocalTableName", "ForeignTableName")
rel.Fields.Append rel.CreateField("LocalFieldName")
rel.Fields.Append rel.CreateField("RemoteFieldName")

rel.Fields.Refresh
Currentdb.Relations.Refresh

In your case, with a 1-to-1 relationship it wouldn't matter which was the LocalTAble and which was the ForeignTable. I have to question, though, why you'd have a 1-to-1 relationship with 2 different fields ... it'd seem to me that, if you're using this to store Notes aobut a Product, you'd have a 1-to-Many relationship.
0
 
LVL 1

Author Closing Comment

by:CMDAI
ID: 31503706
Thank's,
I though relation was a field property, now i know
0
 
LVL 1

Author Comment

by:CMDAI
ID: 22661560
The new table is a discription table for a type of product
I need 1 to 1 as 1 product only has 1 record of desciption
but to make it a subtype i add product category into the relationship:
So in this table the whole category field will be the same
another category will have another table.
oh and notes is just the initial strucure , the user can then add description fields himself
0

Featured Post

Webinar: Choosing a MySQL HA Solution

Join Percona’s Principal Technical Services Engineer, Marcos Albe as he presents Choosing a MySQL High Availability Solution on Thursday, June 29, 2017 at 10:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question