?
Solved

Create table syntax , look up the new table  name

Posted on 2008-10-06
7
Medium Priority
?
371 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
  • 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
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.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

862 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