Solved

Create table syntax , look up the new table  name

Posted on 2008-10-06
7
353 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 84
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

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

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 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