Visual Basic :: Update Multiple Tables In MS Sql

Ado Won't Update Multiple Tables - Status = 64
I have 3 database tables in the following structure:

Users:
id | Name | Surname
1 Bill Bloggs
2 Tony Stamp
3 Claire Dawson

Referrers:
id | Name | Surname
1 Harry Peters
2 Sue Brown
3 Jemma Clythe

Reffered_Users:
User_ID | Referrer_ID | Referral_Date
1 2 15/11/2003
2 2 21/05/2004
3 1 04/06/2004

I was wondering if its acceptable / possible to select or update multiple tables using ado as in:

Code:
sql = "select Users.*, referred_users.* from Users, Referred_Users where referred_users.[user_id] = Users.[id] and Users.[id] = 2"

rs.open sql, objConnection, adopenkeyset, adlockoptimistic, adcmdtext

with RS
'Change Name from Users table from Tony to Alfred
.Fields("Name").value = "Alfred"
'Change refferer_id from Referrer_id table from 2 (Sue Brown) to 3 (Jemma Clythe)
.fields("referrer_id").value = 3
.update
end with

The reason i ask this is because when i tried the above, the RS.Status property was set to 64 - "The record was not saved because it would have affected multiple records", even though the record count was 1.

Is what i'm doing wrong? Is it best practice to write one sql update statement to update the Users table, update then close the recordset, then write another sql statement to update the referred_users table?

How To Update Multiple Fields In Different Tables
Hi

I am writing an accounting application and I came across a different requirement ie.

User want to change the entry in one side and changes should reflect to the other side automatically I will explain this with an example.

let's say the Total Sales is 10,000 and the Total VAT is 1750. Now let's say use found that there was a mistake in the previous entry and the actual figure for the total sale is 20,000 so he want the VAT shoudl automatically change and so as the other accounts for example the wrong customer record entry corresponding to this sale entry should also increase the balance towards that customer.

I am using the mshflexgrid in the most of the forms and user will change the entry by typing in the grid. Now I am stuck that how it will change the other tables in database.

I am using ACCESS as my database. Is it possible thorough procecure or triggers.

Please help me in this case. Also let me know if you require any further detail about this,

Rs.update Problem When Updating Multiple Tables
HI can anyone help me out here. I have a access database with about 10 tables that I have linked to my vb6 project using a DNS. When I try to create a new instance of a recordset and update the values, it says
'You cannot add or change a record because a related record is required in the persons table'

any suggestions?
My database opens on formload.

Importing Multiple Sheets From A Excel Spreadsheet Into Multiple Tables
hello all,

I am trying to code, but i am just stuck after importing one sheet. so here is the gist of what i need help with.

In a workbook at the start of the year (january) i will have 4 sheets and these sheets will keep increasing to 12 when the month is december. so i want is, sheet 1 (named abc) should go into table 1 (named abc), sheet 2 (named def)should go into table 2 (named def), sheet 3 (named xyz) should go into table 3 (named xyz). then sheet 4 through the next sheets till sheet 12 (named balance1, balance2, abalance3...so on till balance12) should all go into one table 'table4' (named balance) and all these sheets should keep appending starting from 1 then 2 all the way to 12.

I am able to put one sheet into one table and below is the code....i need help with the other part of my requirement

Code: ( text )
Dim cn As ADODB.Connection
Dim oRs As New ADODB.Recordset
Dim cnAccess As ADODB.Connection
Dim rsAccess As New ADODB.Recordset

' Open Excel Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:Test.xls;" & _
"Extended Properties=Excel 8.0;"
.Open
End With

' Open Access Connection
Set cnAccess = New ADODB.Connection
With cnAccess
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:Documents and SettingskrishnamDesktopIntercompany Consolidation.mdb;"
.Open
End With

' Load ADO Recordset with Excel Sheet1Data

oRs.Open "Select * from [abc$]", cn, adOpenStatic
MsgBox oRs.RecordCount

' Load ADO Recordset with Access Data
rsAccess.Open "select * from tbl_abc", cnAccess, adOpenStatic, adLockOptimistic
MsgBox rsAccess.RecordCount

'Synchronize Recordsets and Batch Update
Do While Not (oRs.EOF)
rsAccess.AddNew
For i = 0 To 11 -----11 columns in table 1
rsAccess.Fields(i).Value = oRs.Fields(i).Value
Next
rsAccess.Update
oRs.MoveNext

Loop

End Sub

please help me so that i can move forward...

Recalling Data From Multiple Fields In Multiple Tables
I am working on a fairly large project for recording client information into a db which has quite a few fields which are spread out among several tables. The code is already written that saves the data to it's appropriate fields in their appropriate tables.In this particular case, under the front tab of the form is the head person's personal information. On the third tab is information pertaining to the head person's dependents. The code I have written saves the head person's data as well as the data pertaining to that person'd dependents' to a table named [Person]. A few lines lower, those persons' IDs are also entered into an additional table called [Relation], in which are only 2 fields: Relation_Parent_ID and Relation _Child_ID. In another control's code, I recall the data of a given person's ID back into the form from which it was recorded. But how do I code it so that even the children's data is recalled when the parent's data is re-inserted into the form? The code to recall every piece of data that is saved for that person's ID, except for the children's data, is already written and functional. How can I cause the children's data, also, to be recalled when the parent's data is recalled?

Thank you very much for all your help

Happieman
a.k.a. Bill

Edited by - Happieman on 6/11/2005 10:18:40 AM

Multiple .MDF Files With 1 Table OR 1 With Multiple Tables?
In the past, I justed just one .MDB (access) database file with multiple tables, but have run across several commercial programs that have multiple .mdb files with one table. Those tables are then all linked.

Is there reason to go one way or the other and why? If using multiple .mdb files, how do you create the links between the tables in different .mdb files?

--Gary

Updating Multiple Tables With Multiple Recordsets
Hi,
I am trying to update two tables at once, based on the same text box inputs I seem to come close but it will not update the second table. Here is my code:

Option Explicit
Dim Rs As Recordset
Dim Rs2 As Recordset
Dim CN, CN2 As Connection
Private Sub cmdBack_Click()
If Not Rs.BOF Then
  Rs.MovePrevious
End If
If Rs.BOF And Rs.RecordCount > 0 Then
   Rs.MoveFirst
End If
End Sub

Private Sub cmdNext_Click()
If Not Rs.EOF Then
  Rs.MoveNext
End If
End Sub

Private Sub cmdRefresh_Click()
Rs.Requery
End Sub

Private Sub cmdSearch_Click()
Rs.MoveFirst
Rs.Find "LastName = '" & txtSearch & "'"
If Rs.BOF Or Rs.EOF Then
  If MsgBox("The record you entered was not found, Do you want enter another name ?", vbQuestion + vbYesNo, "ID Check") = vbYes Then
       txtSearch = ""
       txtSearch.SetFocus
       Exit Sub
     Else
       Rs.MoveFirst
       txtSearch = ""
       Exit Sub
  End If
Else
'Fills in the data with the fields based on the found data
  txtLastName = Rs.Fields("LastName")
  txtAmount1 = Rs.Fields("Amount1")
  txtAmount2 = Rs.Fields("Amount2")
End If
End Sub

Private Sub Command1_Click()
Dim ST As String
Rs.AddNew ' Add a new record

Rs.Fields("LastName") = txtLastName
Rs.Fields("Amount1") = txtAmount1
Rs.Fields("Amount2") = txtAmount2
Rs.Update

'CurrentDB.Execute ("Update tblName2 Set Last_Name = '" & txtLastName & "',Amounta = '" & txtAmount1 & "',Amountb = '" & txtAmount2 & "'")
Rs2.AddNew ' Add a new record
Rs2.Fields("Last_Name") = txtLastName
Rs2.Fields("Amounta") = txtAmount1
Rs2.Fields("Amountb") = txtAmount2
Rs2.Update

End Sub

Private Sub Form_Load()

Set CN = New ADODB.Connection

CN.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = H:E544293Desktopdb1.mdb;"

Set Rs = New Recordset

Rs.Open "Select LastName, Amount1, Amount2 from tblLastNames", _
CN, adOpenKeyset, adLockOptimistic

Set CN2 = New ADODB.Connection
CN2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = H:E544293Desktopdb1.mdb;"

Set Rs2 = New Recordset

Rs2.Open "Select Last_Name, Amounta, Amountb from tblName2", _
CN2, adOpenKeyset, adLockOptimistic

Set txtLastName.DataSource = Rs
Set txtAmount1.DataSource = Rs
Set txtAmount2.DataSource = Rs

txtLastName.DataField = "LastName"
txtAmount1.DataField = "Amount1"
txtAmount2.DataField = "Amount2"

End Sub

I think it has to do with the adOpenKeyset, adLockOptimistic at the end of the connection string, but if it is this it will not let me take it out or chage it. I also tryed moving things around and that did not woprk.
If any one has any ideas, of waht I am doing wrong, I would really appreciate it
Thank You

UPDATE All Tables
Hi, i have an sql update statement that i want to run on all tables within a database. Heres my current code:

sQl = "UPDATE * SET Note_Line_1 = '" & NoteDB("desc1").Value "' WHERE Bar_Code_Nmbr = '" & TRs("ref").Value & "'"
Con.Execute(sQl)

Yes, I have dimmed the sQl string and all that. My error is when i run the query against the Con connection. Is this syntax correct to query against all (multiple) tables in my database. Somthing is wrong with what i have. Is the asteriks not the correct syntax for selecting all tables?

Thanks,
jakesnake

Update Two Tables In A Mdb
Hi experts,

Table1
ID     Name       Field3
1      name1
2      name2
1      name3
3      name1

Table 2
ID     Name       Field3
1      name2
2      name2
1      name1
2      name3

I want to do two jobs together:
update Table1.Field3 where Table1.ID=2,
update Table2.Field3 where Table2.ID=2 except the record with the same name has been updated in Table1.
The result should be:

Table1
ID     Name       Field3
1      name1
2      name2      update
1      name3
3      name1

Table 2
ID     Name       Field3
1      name2
2      name2      (no, it has done in Table1)
1      name1
2      name3      update

Code For Update Tables
Hi,

i got a performance question for you all.
I want to import a Access DB to an SQL-Server and when im importing i want to transform/check/add some values.

My current query goes like this:

Code:
Set rsAccess = DataConn.Execute("SELECT * FROM TABLE")
Set rsSQL = DataConn.Execute("SELECT * FROM TABLE")
Dim blnEntryExists As Boolean

blnEntryExists = False

Do While NOT rsAccess.EOF

Do While NOT rsSQL.EOF
If rsAccess(0) = rsSQL(0) Then
blnEntryExists = True
End If
rsSQL.MoveNext
Loop

If blnEntryExists = True Then
'Fix some things and make an:
DataConn.rsSQL.Execute "UPDATE TABLE Etc."
ElseIf blnEntryExists = False Then
'Fix some things and make an:
DataConn.rsSQL.Execute "INSERT INTO TABLE Etc."
End If

rsAccess.MoveNext
Loop
As you can see this is going to take forever since the tables
contains 63.000+ entrys. Is there some other way to do this
"the fast way".

Regards

// Peter

Update Tables In Access Via VB
I have two tables in an Access Database. I want to update the first one with new information from the second one via VB. Records not changed must stay intact. At present I am using a SQL Select statement to locate the record to be changed and then update it (ADO). Is there a faster way of doing it? The complete Table has about 8000 odd records with the update table usualy about 500 - 1000 records.

SQL Update Query Using 2 Tables
I have a table in an Access database that I am trying to update. So I have used a SQL statement:

"Update TableName set FieldName = " & Variable

Except the Variable is actually from another table. The tables share a key that is linked to Variable. In other words, if you wrote a select statement you could join the two tables on a related key. Is there any way for me to write one statement to update TableName but using the Variable from the other table?

Thanks in advance,
Eva

How To Update Tables Using Datarepeater.
Hello Everyone,

I am designing a program which displays the Contacts table and the ContatcDetails table in a single form.
The moment user selects a contact name, corresponding company's all contact details are displayed
in the data repeater. Till here it's going smooth, but, the problem is when a Company contacts again,
the data repeater should allow the user to add a new contact. I'm not getting any idea how to achieve this.
Could anyone help me. Any help/guidance is greatly appreciated. Thanks in advance
Kalpana Lloyd

How Do You Update 2 Tables At The Same Time?
I'm using a Data Environment, so every textbox on a form is linked to a field from a single table. Every table has its own form.

For example:

Card Receptions:
-Part Number
-Quantity

Inventory:
-Part Number
-Quantity

Since each table has its own form, I have to update both tables separately every time we receive new cards. I want to automatically update the inventory every time I enter a new box of cards into the Card Receptions table. How can I accomplish that?

Update Query 2 Tables
I have 2 tables

1) Tbl_Employees_JRV
2) Tbl_OT_DAILY_TOTALS_JRV

both have field name OI as the primary key

OT_HOURS_JRV is the running total

I would like to create a update query that will update
OT_HOURS_JRV in Tbl_Employees_JRV when I click on a
button on a form to add REG_OT from Tbl_OT_DAILY_TOTALS_JRV

Thanks in advance for your help

Mike

How To Update The Tables By Reducing The Value Frm Another !
Hai,

The master and detail form Sales and Stock is there. I know to update the Sales Form. But when the user Sold some qty. then the Stock qty in the Stock table for the sold Item should be detected. How we will reduce only the qty sold from the Stock Table.

Thank you,
Chock .

Access Update Tables
hello,
i want to know if i can do next updates in an table.

i have the following columns: CNP, CSV, SCAN and i want to update in a new table like:

1) if CNP found then update CSV field from CNP row with OK
if not found then do not append row at all
2)if CNP found then update on CSV field from CNP row with "errors"
else append new row

i attached a schema with what i want to do, and i want to express your opinion about it.

Regards,

Programatic Update To Multple Tables
Hi,

I have created a DB in Access with multiple tables, defined the relationships and normalised as applicable.

I wish to check for the existance of certain records prior to creation of new records programatically via VBA in Excel and have written code that creates db and recordset objects for the related tables and then parses uses the .findfirst & .nomatch methods/properties to set boolean values of a records existance. A variable is also set that records the value of the primary key /foreign keys of the associated record(s) so that this can be used to update all of the recordsets when an update occurs.

The code works fine but I am interested to know if I am doing it correctly or whether there is a better way. My code basically does something like the following:

Check first recordset for value - if found set variable to value of foreign key of related tables so that these can be used to updated related recordsets.

If not found create new record, record its primary key to variable and then use this variable to update related recordsets.

The point I am trying to make is that I am taking the responsibilty of using variables to create the relationship between tables for new records when programatically updating and it sorta feels like there should be a better way?

If someone would like to see my code I can post it.

1 Stored Procedure To Update 7 Tables?
I've got one parent table with 6 children tables. Data for the tables are entered into a grid through a VB6 program. What I'd like to know is if its better I write one stored procedure to insert/update all these tables or write an individual SP for each table, checking that the record exists in the parent first?

SQL Server 2000 - Tables Update
Dear Sir/Madam,

I am using sql server 2000 and I have a main database named X, and several other databases named Y,Z (For example).

I have to update tables of X with the records of Y and Z (both having similar tables). So I have written a stored procedure to update records and i wanted to use database name with variable (Y as below)

CREATE PROCEDURE [UpdateCIF]

@Y varchar(20)
AS

BEGIN
INSERT INTO TCIF (CID,CIDOrig,AccSerial,Type,Nid,Name1)

SELECT C.CID,C.CIDOrig,C.AccSerial,C.Type,C.Nid,C.Name1 FROM @Y.dbo.cif C,@y.dbo.ADDRESS A WHERE C.CID=A.CID

My problem is even I have passed value for Y the stored procedure is not successful. Please give me a solution to use this variable.

Thanks
Dhama

Update Access Tables W/ A Dbgrid
hi all,
I not new at vb but i'm still learning.
My problem is that i am trying to update a table after the user
clicks on a value from a dbgrid. My code is this:

Dim strWhereUpdate As String
'Update
strWhereUpdate = " Update [Card_Inventory] " & _
" Set Status = 'A' " & _
" Where Control_Num = 'pstrCardConNum' "
datConNum.Database.Execute strWhereUpdate

the str variable in the where clause holds the currently selected
value, the problem is that it doesn't change the status column, but
if I place a literal value there instaed of the variable it changes.
what is wrong with the code.

Tahnks in advance it will be greatly appreciated

A2K Tables And VB: Delay To Update And/or Refresh ?
Hi All,

I am getting crazy....
This code work fine in step by step but at execution time, it looks like a
delay to refresh the A2K table from VB is resulting in some records being
treated more than once.

What is wrong ?
How can I "flush" data in the A2K tables?
Thanks for helping,
Bernard

Private Sub Timer3_Timer()
Timer3.Interval = 0
Dim erlop As Integer, nbrecs As Integer, x As Integer
erlop = 0
Comm1.OutBufferCount = 0
Comm1.InBufferCount = 0
Image1.Visible = Not Image1.Visible
If Comm1.PortOpen = True Then
If Not Data2.Recordset.EOF Then
Data2.Recordset.MoveFirst
nbrecs = Data2.Recordset.RecordCount
While Not Data2.Recordset.EOF
If Data2.Recordset("Sent") = False Then
okout = ""
Comm1.OutBufferCount = 0
Comm1.InBufferCount = 0
Comm1.Output = "at+cmgs=" & Chr(34) &
Data2.Recordset("tonum") & Chr(34) & vbCr & Data2.Recordset("texte") &
Chr(26)
While InStr(1, okout, "OK") = 0 And InStr(1, okout, "ERROR")
= 0
erlop = erlop + 1
okout = okout & Comm1.Input
dummy = DoEvents()
If erlop > 50 Then
erlop = 0
Comm1.OutBufferCount = 0
Timer3.Interval = 1000
Exit Sub
End If
Wend
If InStr(1, okout, "OK") <> 0 Then
Comm1.OutBufferCount = 0
Data2.Recordset.Edit
Data2.Recordset("Sent") = True
Data2.Recordset.Update
Data2.Recordset.Delete
wait (3)
Data2.Refresh
End If
If InStr(1, okout, "ERROR") <> 0 Then
Comm1.OutBufferCount = 0
End If
Else
Data2.Recordset.Delete
Data2.Refresh
End If
If Not Data2.Recordset.EOF Then
Data2.Recordset.MoveNext
End If
Wend
End If
End If
Timer3.Interval = 1000
End Sub

Update Data From Old To New Database For ALL Tables
I'm working on a database offsite. The users are entering in new data (and maybe changing data) into their current database. I need to update ALL the tables in the new database with the information stored in the old database using a Visual Basic procedure. The table structure and names are identical. Each table has a unique identifier or a combination of two unique identifiers.

Idea 1: create some sort of loop using TableDefs and a SQL statement where I can go: "UPDATE TableDefs.Name FROM OldDatabase.TableDefs.Name WHERE ...
Idea 2: delete all the data in the old database, input the data all over again

Does anyone have any ideas on how to approach this? I think I can get started if I can just get the general idea.

Thanks so much, I'm driving myself crazy over this one.

Trying To Update Two Tables At The Same Time With Two Recordsets
Hi,
I am trying to update two tables at once, based on the same text box inputs I seem to come close but it will not update the second table. Here is my code:

Code:Option Explicit
Dim Rs As Recordset
Dim Rs2 As Recordset
Dim CN, CN2 As Connection
Private Sub cmdBack_Click()
If Not Rs.BOF Then
  Rs.MovePrevious
End If
If Rs.BOF And Rs.RecordCount > 0 Then
   Rs.MoveFirst
End If
End Sub

Private Sub cmdNext_Click()
If Not Rs.EOF Then
  Rs.MoveNext
End If
End Sub

Private Sub cmdRefresh_Click()
Rs.Requery
End Sub

Private Sub cmdSearch_Click()
Rs.MoveFirst
Rs.Find "LastName = '" & txtSearch & "'"
If Rs.BOF Or Rs.EOF Then
  If MsgBox("The record you entered was not found, Do you want enter another name ?", vbQuestion + vbYesNo, "ID Check") = vbYes Then
       txtSearch = ""
       txtSearch.SetFocus
       Exit Sub
     Else
       Rs.MoveFirst
       txtSearch = ""
       Exit Sub
  End If
Else
'Fills in the data with the fields based on the found data
  txtLastName = Rs.Fields("LastName")
  txtAmount1 = Rs.Fields("Amount1")
  txtAmount2 = Rs.Fields("Amount2")
End If
End Sub

Private Sub Command1_Click()
Dim ST As String
Rs.AddNew ' Add a new record

Rs.Fields("LastName") = txtLastName
Rs.Fields("Amount1") = txtAmount1
Rs.Fields("Amount2") = txtAmount2
Rs.Update

'CurrentDB.Execute ("Update tblName2 Set Last_Name = '" & txtLastName & "',Amounta = '" & txtAmount1 & "',Amountb = '" & txtAmount2 & "'")
Rs2.AddNew ' Add a new record
Rs2.Fields("Last_Name") = txtLastName
Rs2.Fields("Amounta") = txtAmount1
Rs2.Fields("Amountb") = txtAmount2
Rs2.Update

End Sub

Private Sub Form_Load()

Set CN = New ADODB.Connection

CN.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = H:E544293Desktopdb1.mdb;"

Set Rs = New Recordset

Rs.Open "Select LastName, Amount1, Amount2 from tblLastNames", _
CN, adOpenKeyset, adLockOptimistic

Set CN2 = New ADODB.Connection
CN2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = H:E544293Desktopdb1.mdb;"

Set Rs2 = New Recordset

Rs2.Open "Select Last_Name, Amounta, Amountb from tblName2", _
CN2, adOpenKeyset, adLockOptimistic

Set txtLastName.DataSource = Rs
Set txtAmount1.DataSource = Rs
Set txtAmount2.DataSource = Rs

txtLastName.DataField = "LastName"
txtAmount1.DataField = "Amount1"
txtAmount2.DataField = "Amount2"

End Sub

I think it has to do with the adOpenKeyset, adLockOptimistic at the end of the connection string, but if it is this it will not let me take it out or chage it. I also tryed moving things around and that did not woprk.
If any one has any ideas, of waht I am doing wrong, I would really appreciate it
Thank You

Edited by - Pyash on 2/1/2005 1:40:15 PM

Update A Recordset That Joins 2 Tables
hi,

i have a recordset that joins two tables. Now i want to update a field. But i get a message saying that the recordset is not updatable. I think that this is because i have combined two tables, but i wander if there isn't a way to still update this?

PS i work with access database

HOW TO EDIT AND UPDATE ACCESS TABLES USING VB6 CODING
Hi,
I have 2 tables in access database.( ex: A, B)
In edit mode i am updating Table A with adodc1 and Table B with Adodb Recordset.

my problem is......If I run my program from vb6 it is working fine,
but when I execute/run from exe it is not updating my Table B ..

Need Advice On How To Edit/Update FoxPro Tables From VB
Most of my VB work has been using Access and Native Jet. I'm trying to edit and update FoxPro tables from a VB application. I created an ODBC connection that allows me to view the FoxPro table but I can't save my changes back to it. How do I do this? Any advice would be greatly appreciated.

The 2nd mouse gets the cheese.

Update Sybase Tables Via Visual Basic
We would like to update Sybase tables contained on a
standalone pc via use of a SQL statement executed from a
Visual Basic application.

Can anyone help in telling up how to connect to the tables
via ADO? Also what else would be needed to accomplish this
task? After making the connection, would it be like updating an Access table?

Any help would be appreciated.

How To Lock Tables For Serial SQL Insert/update For VB6-ADO?
Hi, my friends.
I want to insert or update serial tables using sql in VB6 ADO. I hope during insert/update SQL run, these tables should LOCK so that no other operation for these tables. My app intends for multi-users in LAN. The database use Access97 or Access2000.
How I can Lock tha tables and how to Unlock them? or it is necessary LOCK the tables when using SQL insert/update ? Thank you all.

T-SQL Update Statement: Swaping Values Between Tables
I'm trying to write a SQL statament to do a one-for-one swap between my user table and my e-mail table. I'm trying to replace all user names with e-mail addresses, so I'm trying to do an update statement... not working tho.

Code:UPDATE tblUser
SET userName = tblEmail.email
FROM tblEmail
INNER JOIN tblUser.userGUID
ON tblEmail.email

Possible relevant fields in tblUser are: userGUID, fkGUID, userName
Possible relevant fields in tblEmail are: emailUID, fkGUID, email

When I run the syntac check, Query Analyzer says,
"Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'email'."

If someone can lend a hand and explain what I am missing, I would greatly appreciate it.

SQL With Multiple Tables...
Hi...I was just wondering...

I have three tables...Agencies, ProductsOrdered, and Orders.

I have 5 listboxes representing 'Monday' to 'Friday'...what I need to do is go into the database and retrieve the Agencies...plop it into these listboxes according to the day the Agencies place and order which is in the ProductsOrdered table. My question is...do I have to go into the ProductsOrdered table, get the OrderID for orders place through Monday to Friday, open up the Orders table, match the OrderID from the ProductsOrdered table to the OrderID from the Orders table, get the AgenyID from this table and open up the Agencies table and finally get the Agency and populate it into the listboxes!!! This is the long way of doing it, can I just SQL with InnerJoin to do all of this? I hope I'd explained this well enough!!!

THANKS!!!

Multiple Tables
What is the syntax for a select statement that searches through multiple tables?

Multiple Tables
Hi guys,

I was wondering, is there anyways to pull out data from 3 tables? if there is how?

Thank you

Multiple Tables
Hello, I have a design problem. What I am doing is creating n-tear application with ASP.NET using VB and using WEB Services.
My web service is responsible for creating connection object to Oracle database, and retrieving information and put it in to dataset. SQL statements is provided by business module class that is calling web service. This information is then displayed in the datagrid. Once changes are made this dataset gets posted to web service for update/insert/delete.
In most of cases these SQL statements will be connecting multiple tables with WHERE a.pk = b.fk etc…
What is the best way to load this data in to data set, so I can use commandObject to automatically post them back to database? Should I load data in to several tables inside of one dataset with separate SELECT statements? What about displaying multiple tables in the datagrid? Parent-child?
Please help me with this design logic, and with some sample code, for this process.
Thanks,

Database Update - Updating 2 Tables That Are Linked Via Autonumber
Access 2000
VB6

two tables

Table 1

ID - AutoNumber
Field1 - Text

Table 2
ID - AutoNumber
TABLE1_ID - Number
Field1 - Text

Say there is a relationship between Table1.ID and Table2.TABLE1_ID

So when I need to update table2 requires the autonumber generated by Table1 to keep the link.... I understand the principle of using the MAX command to get the highest number but as I am sending SQL commands at the ADODB connection is there not a chance that between the process of updating table1 (sql statement 1) getting the max value (sql statement 2) and then updating table2 (sql statement 3) some sort of referential integrity could have been broken .... I suppose what I am asking is can you lock the database table1 for a period until you have received your max value or can you do this somehow in one big sql statement?

Update Tables Throgh Front End Using Stored Procs
Hi,
Could some one please suggest how can I update tables using stored procedures ( SQL 7) using VB front end. At this stage I am trying to use he Datagrid to update the tables which needs Write access to the tables. Since this sort of access can not be granted to users I am trying work out the solution to use generalised stored procs which needs to be invoked when the user hits the button to update the database. At this stage I am still trying to use the Datagrid ( for easy updation from user's perspective) and
mark the Updated/added/deleted records in the original recordset and send the updated records through stored proc.
This is still under development .It would be great if anyone could provide a better solution

Multiple Tables Summary
Hi,

Here is my problem. I have an excel file with multiple work sheets. In each work sheet i have a table with the following fields: Name, Surname, ID_number. All that I want is to be able to make a new work sheet in which i can generate automaticly a table which contains all students (Name, Surname) who have an ID_number bigger than 100.
PLEASE HELP ME

Self Join With Multiple Tables
Hi, Ive a working Query...
SELECT dbo.TblLookupPreferences.ContactType, dbo.TblEmailAddress.Email, dbo.TblEmailAddress.Name, dbo.TblEmailAddress.Title,
dbo.TblEmailAddress.DoNotMail, dbo.TblLookupPreferences.AssociatedType
FROM dbo.TblAdmMain INNER JOIN
dbo.TblLookupPreferences ON dbo.TblAdmMain.AdminID = dbo.TblLookupPreferences.AssociatedID INNER JOIN
dbo.TblEmailAddress ON dbo.TblLookupPreferences.LookupValue = dbo.TblEmailAddress.EmailID
WHERE (dbo.TblLookupPreferences.ContactType = '7') AND (dbo.TblEmailAddress.DoNotMail = 'N') AND (dbo.TblLookupPreferences.AssociatedType = 'A')

But Now I want to add another table to the same view..

The Other TableName is 'TblProspectmembers'
In need to join tblprospectmembers [Memno] field =
TblLookupPreferences.AssociatedID
WHERE
TblLookupPreferences.AssociatedType = 'M'

but I cant figure out the exact syntax for this.
any help would be highly appreciated!
(oh im using Sql server 2000 and Access 2000 as a front end)

Getting The Count Of Multiple Tables At Once
Is there any way we can get the total count of multiple tables with one sql statement? Something like this:

Select count(Video_ID) As VideoCount, count(Customer_ID) As CustomerCount from Video, Customer

Obviously this doesn't work, I was wondering if there was any correct way of doing this. I would appreciate any help. Thanks.

Reading From Multiple Tables
I can't figure out why my program cannot read records from a certain table in my database. I have 3 related tables that I want my VB program to pull data from. I have 2 of them pulling in fine, however the third table comes up with an error message "No Current Record". I have set up multiple data controls, one for each table, and I am using the seek mehod of the Data Control to connect the controls, using the primary keys in the table. Could someone please look at my code, to double check my work? Maybe I'm overlooking something.

Code:
Private Sub Form_Load()
dtaStateLookup.Refresh
dtaAppointments.Refresh

dtaStateLookup.Recordset.Index = "PrimaryKey"
dtaAppointments.Recordset.Index = "PrimaryKey"
End Sub

Private Sub dtaContactInformation_Reposition()

dtaAppointments.Recordset.Seek "=", dtaContactInformation.Recordset.Fields("ContactID")

If dtaAppointments.Recordset.NoMatch Then
TxtDate.Text = "****"
End If

dtaStateLookup.Recordset.Seek "=", dtaContactInformation.Recordset.Fields("StateAbbreviation")

If dtaStateLookup.Recordset.NoMatch Then
TxtState.Text = "****"
End If

Problem With Multiple Tables
When I open an ADO recordset with JOINs, I couldn't delete (with .delete method) records in the primary table. It gives "Insufficient Column .....". Moreover, on updating value of the key field, the values of the other fields of the secondary table would not be refreshed.

Could anyone give the solution to these problems?

How Do You Make Multiple Tables
I used the below code to make a database with one table. Tried with no success to make an additional table by duplicating the table creation code. Question: How do I create a second table. Also, how do I index it? - Steve

Private Sub builddb()
'
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
CurDir & "airmen_test.mdb"
Dim objcat As ADOX.Catalog
Dim objMyTable As New ADOX.Table

Set objcat = New ADOX.Catalog

objcat.Create strConnection
objcat.ActiveConnection = strConnection

With objMyTable
.Name = "tbl_dom_info"
.Columns.Append "first", adVarWChar, 20
.Columns.Append "middle", adVarWChar, 20
.Columns.Append "last", adVarWChar, 20
End With
objcat.Tables.Append objMyTable

Set objMyTable = Nothing
Set objcat = Nothing

End Sub

Select From Multiple Tables
Hi,

I have three tables:

phone_types: idtype, description
phone_object: id_object, description
phone_number: familyid, idtype, idobject, phone_number

When I choose a family: I want to be able to display
type(description) | object(description) | phone_number

but now on a flexgrid i'm only able to display :
idtype | idobject | phone_number

- is using a flexgrid a good idea?
- how do I change my select?

Select From Multiple Tables
I have two tables in an access database that have a 1 to 1 relationship. I want to pull out all fields from the two related records. Something like this:
"SELECT * FROM PersonalData,Charges WHERE PersonalData.ClientID = Charges.ClientID"
Except this always gives me the last record created which I dont want. I want a specific record. I have the ID number of the record I want in a variable called IDnum. How do I get all fields from both tables for that specific ID number (IDnum)?

"SELECT * FROM PersonalData,Charges WHERE PersonalData.ClientID = " & "' IDnum"' & " = " & Charges.ClientID" (A horrible attempt at guessing) or something like that?

Anybody?

V-lookup Using Multiple Tables
I am looking for some direction on how to solve the following problem...I have a database with 2.3Million line items that I map in several different ways and pull over into an excel pivot table using an OLAP cube. I would like to use a string of v-lookup statements to pull from the various pivot tables. The formula quickly exceeds the size limit for the cell because each cell needs to pull five items the pivot tables.

For example: I want items E4000, 60000, 100ZZ, and X50 to pull into the cells calculation but each of the items is located in a different table. The first digit tells me which table the information is in. The tables are too large to be joined into a single table.

What I was wanting to do but as a VB novice I need some direction is to define the lookup ranges in VB and to reference the range so that I can save space. Is there a way to do this or to define the entire function in VB?

Thanks for the help

Updating Multiple Tables
Hi guys, I need some help please. I have a form which is suppose to record receipts of goods. I am using a datagrid for the data entry process. The form needs to access records from four tables. I have used SQL to do that. After that i am stuck. I would like to have the datagrid automatically put in the Autonumber of the PurchaseInvoice andi would also like the other tables to be updated with the new info. Any help would be appreciated.

Private Sub Form_Load()
Dim rSql As String
Me.Height = 7000
Me.Width = 12000

Set stockCon = New ADODB.Connection

stockCon.ConnectionString = _
"Provider = Microsoft.Jet.OLEDB.4.0; Data Source=C:My DocumentsGP projectgpframings.mdb"

stockCon.Open

Set stockRS = New ADODB.Recordset
'Prepare the recordset.

rSql = "SELECT purchaseinvoice.purchaseinvoiceno, purchaseinvoice.purchaseinvoicedate, purchaseinvoice.purchaseorderno, stock.stockno, purchaseinvoicestock.quantity, purchaseinvoicestock.cost, purchaseinvoice.subtotal FROM ((purchaseinvoice INNER JOIN purchaseinvoicestock ON purchaseinvoice.purchaseinvoiceno = purchaseinvoicestock.purchaseinvoiceno) INNER JOIN purchasesupplier ON purchaseinvoice.purchaseinvoiceno = purchasesupplier.purchaseinvoiceno) INNER JOIN stock ON purchaseinvoicestock.stockno = stock.stockno"
With stockRS
.ActiveConnection = stockCon
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open rSql

End With

If stockRS.BOF And stockRS.EOF Then
Exit Sub
End If

Set tranGrid.DataSource = stockRS
tranGrid.Refresh

End Sub

Multiple Tables In Access
how can I open an Access Db and be able to viewedit all the tables??

The only way I can do it is to open each one separately.

here is my code

Code:
'the name of my table is: Incident
' i do not see how it reads the particular table in the DB
'this code works

Public rsIncidents As ADODB.Recordset

Public CN1 As ADODB.Connection
Public CMD As ADODB.Command

Set CN1 = New ADODB.Connection
CN1.CursorLocation = adUseClient

'---------------------------------------------------------------------------------------
CN1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & "c:mydbicd02.mdb"

'password entry
CN1.Properties("jet OLEDB:Database Password") = "charlyX"

CN1.Open

Set CMD = Nothing
Set CMD = New ADODB.Command

CMD.ActiveConnection = CN1

'Create a query to retrieve all fields from the Incident table, sorted by IncidentDate Ascending
CMD.CommandType = adCmdText

'Db sort mode selected here
CMD.CommandText = "SELECT * FROM Incident ORDER BY INCIDENTDATE"

Set rsIncidents = New ADODB.Recordset

rsIncidents.CursorLocation = adUseServer
rsIncidents.CursorType = adOpenKeyset

'open normally
rsIncidents.LockType = adLockOptimistic

Set rsIncidents.Source = CMD

rsIncidents.Open

'to read a field of the DB i would use code like this
'EntryPerson is a field in the DB

'rsIncidents("EntryPerson").Value & ""

Multiple Tables From One Table
My simple VB 5.0 app uses a large table with many fields. I would like to use many smaller, linked tables for program info, user info, dimension info, etc. for better organization and easier handling. I can create the tables, but how I link them and modify the code below to add new records?

Public Sub RECORD_ADD()
Data1.DatabaseName = "C:ID-VISUAL-BASICsimple-progsp.mdb"
Data1.RecordSource = "USER-INFO"
Data1.Refresh
Data1.Recordset.AddNew
Data1.Recordset("USER-Application") = Mainform.LabelAPP
Data1.Recordset("USER-Description") = Mainform.TxtDescribe.Text
Data1.Recordset.Update
CmdSave.Enabled = False
End Sub

Update Multiple Tables In MS Sql

I want to update two tables with one query.example:Instead of running two queries like this...update contact set contact_id=2 where contact_id=4update appointment set contact_id=2 where contact_id=4I want to run the above queries in one query. Is this possible??Thanks in advance.

I want to update two tables with one query.

example:
Instead of running two queries like this...

update contact set contact_id=2 where contact_id=4
update appointment set contact_id=2 where contact_id=4

I want to run the above queries in one query. Is this possible??

Thanks in advance.