INSERT INTO multiple tables

[James Hartrich]

my Access database has 5 forms and 5 tables(SS# is PK
on all). Form1 has recordsource of an inner join sql and
updates 2 tables. I need form1 to update all 5 tables
with the SS#, so that i can use forms2-5 to update more
info on other tables on the same SS#. I have tried
putting a cmdSave button/event in vba to execute a INSERT
INTO command. (see code below)
I need someone to debug this code and to tell me if i
used the correct sql syntax and if i needed to use
adoConn or recordset objects or not.

Thanking You,

James Hartrich

Campus Info:
SS#, 1_20 Expiration, Work Location, Year of Study,
Academic Recognition, Academic Recognition 2, Academic
Recognition 3, Family Friend, Conversation Partner,
Current Campus Address, Family Text, Conversation Text,
Visa Type


Public Sub sql_insert_into_Campus_Info_Table()

Dim id_buf As String, p_buf As String, co_buf As String,
lab_buf As String, date_buf As String
Dim sqlStr As String, adoCon As ADODB.Connection, adoCon1
As ADODB.Connection, conString As String
Dim msg As String, rs As ADODB.Recordset, rs1 As
ADODB.Recordset, tst As Boolean, co_variant As Variant

On Error Resume Next

'set up the connection string
'conString = "Provider=MSDASQL"
conString = "Provider=Microsoft.Jet.OLEDB.4.0;"
conString = conString & "Data Source="
conString = conString & "C:\Documents and Settings\James
Hartrich\Desktop\international access db\Copy of JSH BETA
6 International_Program.mdb;"

'create the Connection object
Set adoCon = New ADODB.Connection
adoCon.ConnectionString = conString

'open the connection
adoCon.Open

'test for error
If Err <> 0 Then
msg = "an error occured trying to connect to the
International database:" & vbCrLf
msg = msg & "Error number: " & Err & vbCrLf
msg = msg & "Description: " & Err.Description
MsgBox msg
End If
Err.Clear

id_buf = Forms![AppDocs-T]![SS#]
'p_buf = Forms!clock_out!password
'co_buf = Forms!clock_out!clock_out
'lab_buf = Forms!clock_out!lab_id
'date_buf = Forms!clock_out!Date

'SQL query to find user and password in the db*****
MsgBox (id_buf)
'adoCon.BeginTrans

adoCon.Execute ("INSERT INTO [Campus Info] ([Campus Info].
[SS#], [Campus Info].[Work Location]) VALUES(id_buf,
NULL")
MsgBox (id_buf)

'if error, display message then quit
If Errn <> 0 Then
msg = "An error occurred trying to INSERT INTO the
Campus Info TABLE:" & vbCrLf
msg = msg & "Error number: " & Err & vbCrLf
msg = msg & "Description: " & Err.Description
MsgBox msg
'Set rs = Nothing
adoCon.Close
Exit Sub
End If
'adoCon.CommitTrans


adoCon.Close

End Sub

 

[Rick Allison]

James,

Boy oh boy. My guess is that you do not have a normalized database and
that's the root of the problem.

SS# is not good key and since you have it in so many tables as the PK start
there.

Sorry I can't help much more than that.

Rick

my Access database has 5 forms and 5 tables(SS# is PK
on all). Form1 has recordsource of an inner join sql and
updates 2 tables. I need form1 to update all 5 tables
with the SS#, so that i can use forms2-5 to update more
info on other tables on the same SS#. I have tried
putting a cmdSave button/event in vba to execute a INSERT
INTO command. (see code below)
I need someone to debug this code and to tell me if i
used the correct sql syntax and if i needed to use
adoConn or recordset objects or not.

Thanking You,

James Hartrich

Campus Info:
SS#, 1_20 Expiration, Work Location, Year of Study,
Academic Recognition, Academic Recognition 2, Academic
Recognition 3, Family Friend, Conversation Partner,
Current Campus Address, Family Text, Conversation Text,
Visa Type


Public Sub sql_insert_into_Campus_Info_Table()

Dim id_buf As String, p_buf As String, co_buf As String,
lab_buf As String, date_buf As String
Dim sqlStr As String, adoCon As ADODB.Connection, adoCon1
As ADODB.Connection, conString As String
Dim msg As String, rs As ADODB.Recordset, rs1 As
ADODB.Recordset, tst As Boolean, co_variant As Variant

On Error Resume Next

'set up the connection string
'conString = "Provider=MSDASQL"
conString = "Provider=Microsoft.Jet.OLEDB.4.0;"
conString = conString & "Data Source="
conString = conString & "C:\Documents and Settings\James
Hartrich\Desktop\international access db\Copy of JSH BETA
6 International_Program.mdb;"

'create the Connection object
Set adoCon = New ADODB.Connection
adoCon.ConnectionString = conString

'open the connection
adoCon.Open

'test for error
If Err <> 0 Then
msg = "an error occured trying to connect to the
International database:" & vbCrLf
msg = msg & "Error number: " & Err & vbCrLf
msg = msg & "Description: " & Err.Description
MsgBox msg
End If
Err.Clear

id_buf = Forms![AppDocs-T]![SS#]
'p_buf = Forms!clock_out!password
'co_buf = Forms!clock_out!clock_out
'lab_buf = Forms!clock_out!lab_id
'date_buf = Forms!clock_out!Date

'SQL query to find user and password in the db*****
MsgBox (id_buf)
'adoCon.BeginTrans

adoCon.Execute ("INSERT INTO [Campus Info] ([Campus Info].
[SS#], [Campus Info].[Work Location]) VALUES(id_buf,
NULL")
MsgBox (id_buf)

'if error, display message then quit
If Errn <> 0 Then
msg = "An error occurred trying to INSERT INTO the
Campus Info TABLE:" & vbCrLf
msg = msg & "Error number: " & Err & vbCrLf
msg = msg & "Description: " & Err.Description
MsgBox msg
'Set rs = Nothing
adoCon.Close
Exit Sub
End If
'adoCon.CommitTrans


adoCon.Close

End Sub

 

[CSmith]

Hi,

A few things are standing out at me right away...

1. Your SQL is not passing the value of your id_buf variable in your SQL
string. It should be "INSERT INTO...VALUES(' " & id_buf & " ', (rest of your
SQL here)..."

2. Eliminate the ";" at the end of your path string - example:
"...Program.mdb" not "...Program.mdb;"

3. If you're set on using ADO for this (which is fine), then you need to
execute your action query SQL statement using an ADODB.Command object.

If all of these tables have a 1-1 relationship on the SS#, then you should
combine them all into one flat table.

--
Calvin Smith
http://www.CalvinSmithSoftware.com - Automation Code
http://www.SpanglesNY.com - Fendi, Prada, etc - 60% off

my Access database has 5 forms and 5 tables(SS# is PK
on all). Form1 has recordsource of an inner join sql and
updates 2 tables. I need form1 to update all 5 tables
with the SS#, so that i can use forms2-5 to update more
info on other tables on the same SS#. I have tried
putting a cmdSave button/event in vba to execute a INSERT
INTO command. (see code below)
I need someone to debug this code and to tell me if i
used the correct sql syntax and if i needed to use
adoConn or recordset objects or not.

Thanking You,

James Hartrich

Campus Info:
SS#, 1_20 Expiration, Work Location, Year of Study,
Academic Recognition, Academic Recognition 2, Academic
Recognition 3, Family Friend, Conversation Partner,
Current Campus Address, Family Text, Conversation Text,
Visa Type


Public Sub sql_insert_into_Campus_Info_Table()

Dim id_buf As String, p_buf As String, co_buf As String,
lab_buf As String, date_buf As String
Dim sqlStr As String, adoCon As ADODB.Connection, adoCon1
As ADODB.Connection, conString As String
Dim msg As String, rs As ADODB.Recordset, rs1 As
ADODB.Recordset, tst As Boolean, co_variant As Variant

On Error Resume Next

'set up the connection string
'conString = "Provider=MSDASQL"
conString = "Provider=Microsoft.Jet.OLEDB.4.0;"
conString = conString & "Data Source="
conString = conString & "C:\Documents and Settings\James
Hartrich\Desktop\international access db\Copy of JSH BETA
6 International_Program.mdb;"

'create the Connection object
Set adoCon = New ADODB.Connection
adoCon.ConnectionString = conString

'open the connection
adoCon.Open

'test for error
If Err <> 0 Then
msg = "an error occured trying to connect to the
International database:" & vbCrLf
msg = msg & "Error number: " & Err & vbCrLf
msg = msg & "Description: " & Err.Description
MsgBox msg
End If
Err.Clear

id_buf = Forms![AppDocs-T]![SS#]
'p_buf = Forms!clock_out!password
'co_buf = Forms!clock_out!clock_out
'lab_buf = Forms!clock_out!lab_id
'date_buf = Forms!clock_out!Date

'SQL query to find user and password in the db*****
MsgBox (id_buf)
'adoCon.BeginTrans

adoCon.Execute ("INSERT INTO [Campus Info] ([Campus Info].
[SS#], [Campus Info].[Work Location]) VALUES(id_buf,
NULL")
MsgBox (id_buf)

'if error, display message then quit
If Errn <> 0 Then
msg = "An error occurred trying to INSERT INTO the
Campus Info TABLE:" & vbCrLf
msg = msg & "Error number: " & Err & vbCrLf
msg = msg & "Description: " & Err.Description
MsgBox msg
'Set rs = Nothing
adoCon.Close
Exit Sub
End If
'adoCon.CommitTrans


adoCon.Close

End Sub