Solved

Create table syntax , look up the new table  name

Posted on 2008-10-06
7
325 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now