Help needed on

[Jon Tillman]

Here is my problem:

1) I have two tables Table1 and Table2 where Table1 contains a Text
field of references to Primary Keys in Table2 in the form "5,6,8,9"

2) I have created a new table, Table3 to store this information properly
as columns "Table1ID" and "Table2ID"

How do I actually go about getting the Text string out of Table1 and
into Table3 as N number of records?

 

[Douglas J. Steele]

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim lngLoop As Long
Dim strSQL As String
Dim strKeys As String
Dim varKeys As Variant

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("Select ID, Table2IDs FROM Table1")
Do Until rsCurr.EOF
strKeys = rsCurr!Table2IDs
varKeys = Spit(strKeys, ",")
For lngLoop = LBound(varKeys) To UBound(varKeys)
strSQL = "INSERT INTO Table3 (Table1ID, Table2ID) " & _
"VALUES (" & rsCurr!ID & ", " & varKeys(lngLoop) & ")"
dbCurr.Execute strSQL, dbFailOnError
Next lngLoop
rsCurr.MoveNext
Loop
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

 

[Albert D. Kallal]

You have to write code. (and, if users were slopply, and things like "extra
spaces"

"6 ,8,9"

6,8,,9"

etc.

Will have to be dealt with via extra coding.

However, we will assume for the example that your users were like perfect
computers, and ALWAYS entered the data 100% consistanct as per your example:

eg:

5,6,8,9

If you data is not 100% perfect that way, then the code will crap out...

You code will look like:

Public Sub MySplitOut()

Dim rstOutput As DAO.Recordset
Dim rstInput As DAO.Recordset
Dim vIdList As Variant
Dim vID As Variant

Set rstOutput = CurrentDb.OpenRecordset("table3")
Set rstInput = CurrentDb.OpenRecordset("select id,IdList from table1")

Do While rstInput.EOF = False

vIdList = Split(rstInput!IdList, ",")
For Each vID In vIdList
rstOutput.AddNew
rstOutput!FKid = rstInput!ID
rstOutput!FValue = vID
rstOutput.Update
Next
rstInput.MoveNext
Loop
rstOutput.Close
rstInput.Close


End Sub

Warning...the above is untested air code, but it should give you the overall
idea...

In the above I assumed that the PK field is "id" (the default for tables
primary key (Pk) pk anyway), and I assumed that the foreign key (fk) in the
child table is FKid.

You will of course test this on a copy...since any code that updates data is
dangerous...

 

[Jon Tillman]

Thanks for the pointers. I'll go off now and see what I can come up
with. Fortunately, the data is consistently formatted, so that's one
less headache...

 

[Jon Tillman]

Moving right along, I managed to get a modified version (with null
checking) to work, so thank you!

Now I back up one step and look to create the Table3 through SQL. Here I
run into another problem.

Working in Access 2007, the following will create a table:

CREATE TABLE Blend_Contents_Test
(
ID AUTOINCREMENT Primary Key,
BlendID INT,
ContentsID INT,
);

but that table lacks foreign key constraints. However, the following
will not work due to "Cannot find table or constraint":

CREATE TABLE Blend_Contents_Test
(
ID AUTOINCREMENT Primary Key,
BlendID INT,
ContentsID INT,
CONSTRAINT FKBID FOREIGN KEY (BlendID) REFERENCES tblTobacco(TobaccoID)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT FKCID FOREIGN KEY (ContentsID) REFERENCES
tblContents(ContentsID) ON UPDATE CASCADE ON DELETE CASCADE
);

Removing the ON UPDATE CASCADE ON DELETE CASCADE bits doesn't work either:

CREATE TABLE Blend_Contents_Test
(
ID AUTOINCREMENT Primary Key,
BlendID INT,
ContentsID INT,
CONSTRAINT FKBID FOREIGN KEY (BlendID) REFERENCES tblTobacco (TobaccoID),
CONSTRAINT FKCID FOREIGN KEY (ContentsID) REFERENCES tblContents
(ContentsID)
);

gives the same error.

Any ideas where I am going wrong here?