Multiple Tables
Hi guys,
I was wondering, is there anyways to pull out data from 3 tables? if there is how?
Thank you
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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
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
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,
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?
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=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.
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
Creating Multiple Tables From One
I'm searching for a method of creating multiple like tables from a single larger table in MS Access based upon the contents of a data element changing. Please help
SQL Satement From Multiple Tables
I've got a DB with a Product table and a Campaigns table that describe two types of product lines.
In a search page, I need to be able to search by a given ID #, which is chrProductId in the product table, or campaignCode in the Campaigns table.
I use LIKE statements, so if someone searches for 100, they get all results that have '100' somewhere in their product id.
I had
Code:
SELECT * FROM PRODUCT p, CAMPAIGNS c WHERE c.campaignCode = '15-15001' OR c.campaignCode = '15-15001'
but of course, this will give all the results from both tables whenever one of the conditions is met (in this case, 15-15001 is a campaign code, so there should be no results from the product table portion, just the campaign results). For a partial search string (like "15"), you could get results from both tables.
ANy ideas?
MsHflexgrid... Multiple Tables...
yo...
i was wondering if it is possible to use this:
Code:
If cboContact.Text = "-Alle-" Then 'if i must... get ALL the data...
strSQL = "SELECT * FROM " & cboContact.List(1)
For i = 2 To (cboContact.ListCount - 1) 'the other tables...
strSQL = strSQL & " UNION SELECT * FROM " & cboContact.List(i)
Next
strSQL = strSQL & " ORDER BY VerloopDag ASC,VerloopTermijn ASC "
Else 'or... just select from the selected TABLENAME...
strSQL = "SELECT * FROM " & cboContact.Text & _
" ORDER BY VerloopDag ASC,VerloopTermijn ASC"
End If
MyRecSet.Open strSQL, MyConn, adOpenStatic
Set MSHFlexGrid1.DataSource = MyRecSet
when i execute...
and click on "-Alle-" in the cboContact (combobox)...
it gives me nothing...
- when i select "-Alle-"...
- strSQL = "SELECT * FROM tom UNION SELECT * FROM tombo ORDER BY ...."
thats all good, right?
then why wont it work??
Datagrid With Multiple Tables
I have an application that includes a datagrid. I would like to add the option for the user to select a table from a dropdown combobox and have the datagrid populate based on that table name. All the tables are located in the same database and will have the same columns, just different values. Is there an easy way to change these tables within the same datagrid?
Thanks in advance for any advice!
Querying Multiple Tables
i have two problems with the following query:
Code:
SQLstmt = ""
'
' SQLstmt = "Select branch_index.region, sales_sum.user, sales_sum.total," _
' & vbCrLf & "sales_sum.short_desc, sales_sum.accepted, sales_sum.denied into temp" _
' & vbCrLf & "from branch_index, employee_data, signon_mstr, sales_sum" _
' & vbCrLf & "where sales_sum.user = signon_mstr.memo_id" _
' & vbCrLf & "and signon_mstr.dept_branch = employee_data.dept" _
' & vbCrLf & "and employee_data.cost_center = branch_index.branch_num"
1.) I am returning into a temp table when all i really need is to add info into my original sales_sum table.
2.) also, each record 63 times.
I need a region for each person in the sales_sum table.
Unfortunately, this is never an easy task.
there is no table that associates a username with a region in our master databases.
so what i have to do is grab each username, look in the sign_on table to get their department number, then use their department number to find the cost_center in the employee_data table, and finally use the cost_center to find the region in the branch_index table.
man what a nightmare for a darn 4 digit number.
Getting Data From Multiple Tables
I have a DB w/ 3 tables in it.
Suppliers
Products
Trans
SupplierID is in both the Suppliers and Products Tables,
Three combo boxes named SupList, MatList and TransList
What I am trying to do is to select a supplier, in turn that will automattically
retreive the Materials for that Supplier and populate the Materials combobox.
Can some one get me in the right direction?
Option Explicit
Private MyConn As ADODB.Connection
Private MyRecSet As ADODB.Recordset
Private Sub Form_Load()
'Hide ID Columns
SupList.Columns(0).Visible = False
MatList.Columns(0).Visible = False
TransList.Columns(0).Visible = False
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "DataDelPro.mdb"
MyConn.Open
Set MyRecSet = MyConn.Execute("SELECT * FROM Suppliers")
With MyRecSet
Do Until MyRecSet.EOF
SupList.AddItem MyRecSet.Fields("SupplierID").Value & ";" & UCase$(MyRecSet.Fields("SupplierName").Value)
'Below 2 lines not being used at this time till I get first to work
' MatList.AddItem MyRecSet.Fields("ProductID").Value & ";" & MyRecSet.Fields("ProductName").Value
' TransList.AddItem MyRecSet.Fields("TransID").Value & ";" & MyRecSet.Fields("TransName").Value
.MoveNext
Loop
End With
MyRecSet.Close
Set MyRecSet = Nothing
End Sub
Deleting From Multiple Tables
Hi,
I have a question: I have a database that contains 5 tables. How can I delete the contents of that 5 tables at the same time? Can I use
"delete * from *"? I've actually tried it but I always receive an error. What should I use?
Thanks...
Results From Multiple Tables
i have an access database holding employees. every employee has the exact same fields but they are in different tables according to the department they work in. i want to include a search function in my app that would return results from all tables in the database based on a last name but im not sure of the SQL syntax. Can anyone help?
Insert To Multiple Tables Ado
Hi all,
I am developing a multi user system. Therefore I prefer to use SQL. Hence the doubt that how do I use the SQL to add the data to mutiple tables simultaneously when I click a button. Can anybody please help me.
Thanks
Need Help With SQL Query For Multiple Tables
Hello,
I am trying to do a SQL select query on two different tables in a single access database (.mdb) file I have. This is what I have:
Table 1 name: Jan2006
---------------------------------------------
StnCodeCustomerNoName1 Name2
012100200001HARWANT REALTY SDN BHD
012100200003EUROPE KITCHEN STATION
012100200004COLISEUM CAFE & HOTEL
012100200005SKT V K KALYANASUNDRAM
012100200007F T LAND CARS SDN BHD
012100200008CYCLE & CARRIAGE BINTANG
012100200010YAYASAN UBAIDI
Table 2 name: 24Months
---------------------------------------------
CustomerNo
00200002
00200003
00200004
00200005
00200006
00200008
00200011
My aim is to use the CustomerNos in Table: 24Months and extract the whole row (data) in Table1 corresponding only to that particular customer no. I am trying to use SQL query, here is what I have. I am trying to use inner join,
however have not yet achieved my desired output. Can anyone help me with this? All help is appreciated.
Code:
sql6(lngPosition6) = "SELECT Jan2006.CustomerNo, Jan2006.Name1, Jan2006.Name2, " _
& "24Months.CustomerNo FROM Jan2006 INNER JOIN 24Months ON Jan2006 " _
& "Jan2006.CustomerNo = 24Months.CustomerNo"
Listview And Multiple Tables?
Hi, this is my first thread and I'm pretty new to vb6.
At present I'm working on a search function using a listview.
Could someone tell me if there's a way to use multiple tables to populate the listview.
Heres my atttempt.
Private Sub Form_Load()
Call Connect
check_rs
rs.Open "select * from music", cnn
rsPub.Open "select * from Publishers", cnn
fill_list
End Sub
Public Sub fill_list()
ListView1.ListItems.Clear
If rs.RecordCount = 0 Then Exit Sub
While Not rs.EOF
Set lst = ListView1.ListItems.Add(, , rs(0))
For eX = 1 To 12
lst.SubItems(eX) = rs(eX)
Next eX
rs.MoveNext
Wend
End Sub
No joy though. Error 3265.
Multiple Tables Needed
Hi,
Going back to a previous thread i sent, i could take data from one access table and tranfer to excel.
I made some changes so this could work with many tables.
But I get this error: "Select method of Range class falied"
Also, using Stored Procedure, I could load data from excel to one access table. I dont know what changes I need to make so it could work with many tables.
Anyone help me make the changes Properly please? I've attached the files
Thanks a lot.
INNER JOIN With Multiple Tables
hi,
i've searched a lot but couldn't find anything. I've 4 tables
table1: f1, f2, f3, f4
table2: f1, f2
table3: f1, f2
table4: f1, f2
i want to join theese tables so that the result will look like this;
table1.f1, table2.f1, table3.f1, table4.f1
the join conditions are
table1.f2 = table2.f2, table1.f3 = table3.f2, table1.f4 = table4.f2
i can join two by two but not 4 in the same time
can anyone help me?
SQL Query On Multiple Tables
Hello,
I am currently a newbie to VB 6.0. I am trying to do a SQL select query on two different tables in a single access database (.mdb) file I have. This is what I have:
Table 1 name: Jan2006
---------------------------------------------------------
StnCode CustomerNoName1 Name2
0121 00200001 HARWANT REALTY SDN BHD
0121 00200003 EUROPE KITCHEN STATION
0121 00200004 COLISEUM CAFE & HOTEL
0121 00200005 SKT V K KALYANASUNDRAM
0121 00200007 F T LAND CARS SDN BHD
0121 00200008 CYCLE & CARRIAGE BINTANG
012100200010YAYASAN UBAIDI
Table 2 name: 24Months
---------------------------------------------
CustomerNo
00200002
00200003
00200004
00200005
00200006
00200008
00200011
My aim is to use the CustomerNos in Table: 24Months and extract the whole row (data) in Table1 corresponding only to that particular customer no. I am trying to use SQL query, here is what I have. I am trying to use inner join,
however have not yet achieved my desired output. Can anyone help me with this? All help is appreciated.
Code:
sql6(lngPosition6) = "SELECT Jan2006.CustomerNo, Jan2006.Name1, Jan2006.Name2, " _
& "24Months.CustomerNo FROM Jan2006 INNER JOIN 24Months ON Jan2006 " _
& "Jan2006.CustomerNo = 24Months.CustomerNo"
Best Way To Update Multiple Tables
Hi.
I'm wondering what the best way is to update multiple tables. Right now I have 4 different Update queries to update 4 different tables. It is killing out my program with all the work it has to do. All the tables are 10,000+ records long so you can imagine the time it takes to do each query. Well thanks for your help.
TIA
-Mike
Multiple Tables In Word Using VB6
Ok I am trying to create mulitple tables in a word document created in a VB6 dll, on a server and save it.
I have tried the following code which works on my machine but not the server
Windows XP(workstation) and Windows 2000 server.
Tried the following two lines.
objDoc.ActiveWindow.Selection.TypeParagraph
objDoc.ActiveWindow.Selection.MoveDown
For some reason it just leaves the coursor in the table cell and creates a new table in that cell, which is what i want i want a new table created after it.
The follwoing line creates the table
Set table = objDoc.Tables.Add(objWord.ActiveWindow.Selection.R ange, irowcount + 1, 3)
Any help on hwo to move out of the table to create a new one below would be great, i have already spent one day on this not happy.
Thanks Craig
Database With Multiple Tables, Help Please.
Hello again,
I would like to get some ideas on how to create a database with many tables. I'll try to explain with an example.
Use a Inventory type example, for each item in stock you'd have a table and a form to add/edit records to the table each day or whenever. However, you'd have a couple of hundred tables in the database. The tables must also link to each other by means of Sales or incoming stock. For instance, when you make a sale, the corresponding tables must be updated accordingly, as well as when you increase your stock.
I was thinking of having the list of tables show in a listview or combobox, when an item is selected, it would be activated for adding/editing records , etc.
I hope to get some ideas or a different approach to this.
If anyone knows of an example project or any advice it's greatly appreciated, thanx!
Cheers
RecordSelectionFormula For Multiple Tables
Hi,
No reply for my last post!!??
Plese anybody can tell me how to write RecordSelection Formula in VB6 for multiple tables in MS SQL 2000 for Crystal Report ver. 7.0? In my project I am using 8 tabes to print a report. but I am not getting required result.
--
Regard,
Rajeev Vandakar
Select From Multiple Tables
Hi everyone,
I'm trying to write a select statement that grabs information off of multiple tables. These tables are identical in that they have the same field names/types. I need to select data from one table, and if there are no results there, I need to select from the second and so on. I was wondering if there is a way to check all of these tables with one sql statement instead of having to use vba to loop and do muliple queries?
Edited by - Guyperson on 8/28/2006 11:59:21 AM
Multiple Detsination Tables In DTS
Hi,
Any input of how to move data from one source table to multiple destination tables ( Foxpro to SQL Server 2000)
Any help in this regard greatly apprecaited
thanks
gans
Print From Multiple Tables
Hello gurus,
I have a database, which contains these tables,
SalesMaster,SalesDetails,WaitorMaster,MemberMaster.
There's one form into which the data is entered which gets stored into SalesMaster and SalesDetails.
When I want to take a print out, I have made a crystal report, which gets data from this, exports this to the printer directly, everything is working fine.
But the amount of time which is taken to print is too much, also, in the SalesMaster, at the moment, I found round 2000 records, SalesDetails contains round 7500 records,
what I observed, is the time taken to read the data is bit more.
and also how many records can be fit into a single table so that it works fine and fast.
Generally, projects done through say Dbase, doesn't take much time to print the data,
but why is this Access or Crystal Taking so much time to print out?
This is cracking my head out.
Any gurus can pls help me out.
Regards,
Shivakumar G.M.
Multiple Tables Data In CR
I have different four tables having records. I want to generate report from these tables via Visual Basic program. But when one table data Complete to print in CR some sataments may print through Parameter passsing from VB. This way four tables data print gradually in one CReport.
Thanx in Advance
Combine Multiple Tables With Same Attributes
I'm trying to combine 3 tables that all contain the same fields but different data. Basically I am running a macro that creates the three tables and then allocates them each separately. I want the macro to finish by bringing all the tables together into a final table that contains all the data.
I wan to write some sort of module for this process, maybe some sort of union or inner join, just not sure how to do it.
Any suggestions would be great.
Creating Reports With Multiple Tables
OK, I'm not going to lie....this is my first attempt at trying to create a report using access. How do you link tables into a report?
In my design view I see how you can bound the text fields to a field in the table, but How do I bound to other tables? Do I have to build an expression or query?
Here is what I need to do for example. I need to put the name of the person on the report however, he report I built is from a froeign key table so there is just an ID. How do I get the person's name that has that ID to show up on my report?
Add Records To Multiple Tables(FootballPool)
I have figured out how to add a new record to ONE table in an Access database.
I need to add same records to MULTIPLE tables in same Access database.
In other words...
In my football program wanted to create an add players to pool form.
I have done so, yet can only add records for the new player in one table (Week1, or whatever I put as data source for an ado object).
I have a different table for each week in the nfl season. So Access database has a form "Week1", a form "Week2"...and so on.
Each form has a field "Player name" along with fields:"Pick16" thru"Pick1"
I want my form in VB application to add players to the pool, thereby adding The players name into the "Player name" field in each of the tables representing the 17weeks in an NFL football season.
I hope I've been clear enough...I know that all of you know how difficult it can be to explain what it is that you have trouble with.
Thanks in advance for your help!
Ryan
Data Report With Multiple Tables
Good morning!
I have a VB application that loads information into Access tables about patients into a Client table, and then supporting information (such as allergies, prescriptions, contacts) into separate tables. There is generally a one-to-many relationship between my client table and the supporting tables. I've inserted a data report into my application to print the information about the patients along with all the supporting information. My instructions said to use the Data Environment designer as a data source. I can select fields from the Data Environment and move them to my data report from the client table and one supporting table, but when I try to select a field from the third table in the Data Environment, I get the round graphic with the slash and VB wont let me drop it on the report.
Am I asking too much of the Data Environment and data report? If so, how do I proceed in getting this information into a report?
Can I use the Data Report without Data Environment and load the report fields with VB code like a form?
Thanks for your help,
Data Report And Multiple Tables
I have been struggling with the Data Report part of VB6 for awhile now. I do have a data environment and data environment connection with no properties set. I am trying to do everything with code and at run-time. I have a report setup right now with a bunch of headings and data fields that are just typed in. My problem is that I need to pull info from multiple tables in one database and display it in the same report. My other problme is that the tables I am pulling info from have some of the same field names. My last problem is that I can't seem to figure out how to do this. Here is the code I have now that I basically got from MSDN but it does pull info from one table and work.
Code:
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Set CN = New ADODB.Connection
Set RS = New ADODB.Recordset
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
App.Path & "" & dbName
CN.Open connectionString
Set RS.ActiveConnection = CN
RS.Source = "Select * FROM Drivers"
RS.Open , , adOpenStatic, adLockOptimistic
Set rptRaceAll.DataSource = RS
rptRaceAll.Show
I did think that the table.field method might work somehow but I am not sure where or what. If anybody can help me with this I would be very thankful. Also, if anybody knows of any good resources on the data environment and data reporter and doing things at run-time with them please pass it on. Thanks alot!!
-Scott
Queries On Tables In Multiple Databases
I want to compare inventory data in an Access database to similar data in another Access database. I want to do this without importing data or making fixed links in either of the databases.
I am using Acces/VB6/ADO.
How do I link a table from one database into another using VB, so that I can run a query on it. Or alternatively how can I make a query on tables in two different databases.
(I believe to have seen something similar to the following but do not remember where. In any case it is not very ADO.)
SELECT Countries.CountryName, Regions.RegionName
FROM Regions INNER JOIN Countries in [Access: c:my databasesdb.mdb] ON Regions.RegionCode = Countries.RegionCode;
Many Thanks
Pete
Working With Multiple Tables From Different Databases
How does one design a select statement using three tables all on different databases?
One is an MS SQL server table on a database that is not the default database. The table is called "CHOOSEN" and the database is called "WORK". (Field called id is linked to a field called clientid below)
There is a MS Access table that is shared on our network drive called "ADDRESSES" and it is in the database "S:ClientsCLIENTINFO.mdb"
(Field called clientid is linked to id in CHOOSEN above, field called zipcode is linked to uszips.dbf to a field called zips below)
Finally I've got a local table called uszips.dbf in my App.path where I'm designing the VB Project. (Has a field called zips and is linked to ADDRESSES above through the field called zipcode)
Can I design an SQL statement in VB so I can get data from all three tables? I can do it in Access if I simply link all the tables into the access database but I would like to do this with code.
|
TRACKER
