?
Solved

Create table syntax , look up the new table  name

Posted on 2008-10-06
7
Medium Priority
?
362 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
Technology Partners: 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In this article, we’ll look at how to deploy ProxySQL.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 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