Visual Basic :: Reading From Multiple Tables

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

Reading Tables From .doc
hello

i have a large .doc with a large table in it (just table). i need to read the values contained in the .doc's table. how on earth do i do this? i tried opening the .doc in notepad and saw just a load of garbage, the formating of .doc isnt as clear as of say .csv. any ideas? there must be a way.

thnx

iam using vb6 and word 2002 btw

Reading DB2 Tables With VB6
Hi Everyone,
I have been programming in VB6 for quite some time now. I have been building many applications with Access databases. The company I work for keeps all information in DB2, oracle, and SQL servers. Normally, to access any information from the DB2 tables, i have to first pull it into access using macros. then, i can read it with my VB from the ACCESS. is there a way to read directly from the DB2 tables with VB6?

SQL Reading From 3 Tables
Hi There,

I can't believe it but I cannot get my head past this problem. Using VB6 I am trying to count and sum from the Access Table named "TABLE1". However I only want to evaluate those records that correspond to the condition from table named "prefs". There is also seperate information contained in table "Para" that I need to access. There 44 records in "Table1", 1 Record in "Prefs" and 2 records in "Para". If I try to do a count of those records in Table1 which match the criteria from "Prefs" I get double the count, i think because there are 2 records in "Para". If I add another record to "para" I then get a triple count. Is there any way around this. Any help would be much appreciated.

sQL = "SELECT * " _
& " FROM table1, para, prefs" _
& " WHERE custo = lastuser" _
& " ORDER BY date; "

rs.Open sQL, sConn, adOpenKeyset, adLockOptimistic, adCmdText

Reading Through HTML Tables
Is there a way to read through HTML tables and pick out
records that someone has chosen.

Ex.

Selected Order# Price

Reading DBase (*.dbf) Tables
I've been wrestling with reading dBase tables. I've successfully read
MSAccess, MSExcel, *.csv, and *.txt files. If I understand correctly
(likely no) I need to install a copy of the Borland Database Engine (BDE).

Is it true that I need BDE?

If so:
1) Where do I get the BDE driver (hopefully cheaply)?
2) How do I connect using it?
3) Could someone hook me up with working code to read a dBase table?

Thanks for any help you can offer.

Reading From Two Separate Tables
Hey everyone.

I am having a problem with reading from two separate tables in the one database.
What I would like to do is...I have one database called 'Users' and different databases that have the users' names. What I want my program to do is:

1. read from the users database
2. open the database for that user
3. get a value from that users' database
4. close that users' database
5. read the next user from the users database
6. continue the process until all the user databases have been open from the users database

My current code works however it just performs a loop with the first user in the users database.

My code looks like follows:

Code:Option Explicit

Public RX As String
Public MaxCN As Long
Public InitMax As Long

Dim GrossWorth As Long

Dim UserNamePayment As String
Dim PaymentExtension As String
Dim DBOPEN As String

Dim con As ADODB.Connection
Dim WithEvents rs As ADODB.Recordset

Dim iCount As Long

Dim lItem As ListItem

Private Sub CalculateGross_Click()

GrossWorth = Empty

Call OpenUsers

Call CalculateGrossWorth

End Sub

Sub OpenUsers()

MsgBox "OpenUsers called"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Dim SQL As String

rs.Open "Select * from Users", cn, adOpenForwardOnly, adLockReadOnly

For iCount = iCount To rs.RecordCount




     cUsers.AddItem Rs!Username

  MsgBox "Decrypt username: " & Rs!Username

UserNamePayment = Empty
PaymentExtension = Empty
DBOPEN = Empty

UserNamePayment = Rs!Username
PaymentExtension = "_Payment"

DBOPEN = UserNamePayment & PaymentExtension

Call OpenDb


  rs.MoveNext

Next iCount

rs.Close

Set rs = Nothing

End Sub

Sub OpenDb()

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim i As Long

Dim SQL As String

'this string holds the date entered by the user to search
Dim Selected As String

Selected = Text1.Text

SQL = "Select * from " & DBOPEN & " where Date = " & Chr(34) & Selected & Chr(34)

rs.Open SQL, cn, adOpenKeyset, adLockReadOnly

MsgBox "Open database: " & Rs!Username

'If rs.EOF = True Then GoTo CallNextUser: rs.Close

GrossWorth = GrossWorth + rs!TotalAmount

MsgBox GrossWorth

iCount = iCount + 1

'On Error GoTo CallNextUser: rs.Close

Call OpenUsers

End Sub

Sub CalculateGrossWorth()

MsgBox GrossWorth

End Sub

Private Sub Form_Load()

Dim sSQL As String

MaxCN = 0

DBConnect

If DBConnect = True Then

sckServer(InitMax).LocalPort = "9456"
sckServer(InitMax).Listen

Call LtUsrs

Else

End

End If

iCount = Empty
iCount = 1

Call UserList

End Sub

Sub UserList()

Dim deUsername As String

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim i As Long

rs.Open "Select * from Users", cn, adOpenForwardOnly, adLockReadOnly

For i = 1 To rs.RecordCount



     cUsers.AddItem Rs!Username


  rs.MoveNext

Next i

rs.Close

Set rs = Nothing

End Sub

Edited by - lianp on 2/4/2005 12:50:15 AM

Reading Data From Tables In Word
Ive tried everything i can think of and im stuck. Can someone please help me. I need to be able to read data from tables in Word97, no problem i can do this. However the only way i can access the tables is by using:
While Not endOfTable
UpdateActivityBar ProgressBarActivity.ActiveBar, 0.1
Count = Count + 1
If Not endOfTable Then
endOfTable = wrdDoc.Tables(curTable).Rows(Count).IsLast
End If
Wend

tblOneSize = Count
For Count = 2 To tblOneSize
UpdateActivityBar ProgressBarActivity.ActiveBar, 0.1
tmp = wrdDoc.Tables(curTable).Columns(1).Cells.Item(Count)
tmpStr = tmp.Text
tmpStr = trimData(tmpStr)
tblData.colOneData = tmpStr
tmp = wrdDoc.Tables(curTable).Columns(2).Cells.Item(Count)
.
.
.
The problem is that the format of the document is not under my control so one time the information i need may be in table 1 and the next time it may be in table 5. How can i use captions or headings as triggers to get to the table i need?

Reading All Tables In A Word Document
hi all,

I am working on a project(VB 6) which requires to read many tables from a
word document.
The tables can come in any page(cant be sure which page) & the number of
tables can also vary
How can i read them in a loop.. Or is there any other method to read all of
them ?
[ I think the trick is to get number of pages in word (which im not able to
get ) n loop thro "tables(pagenumber)" ]

Thanx
Vipin

Reading FoxPro Free Tables
How can I can an ODBC link to read 'Free' Foxpro tables
( i.e. not accessed via a .dbc container )?

I have no prior knowledge of Foxpro and have to read data into VB from two Foxpro .dbf files.  I have found plenty of web sites and FAQ's with the connection string format that points an ODBC link to a folder that contains the two .dbf files.

"Driver={Microsoft Visual FoxPro Driver};" & _
             "SourceType=DBF;" & _
             "SourceDB=c:FoxPro Files" & _
             "Exclusive=No;"

I do not however have an example of how to get data out of the tables.  The connection works fine at run time but how do I populate a recordset from two .dbf files and identify the field names within them?  I tried specifically referencing the .dbf filename in the above connection string without any success.

I thing there is a simple way of doing it but it eludes me!

I do have checked I have the up to date Foxpro ODBC driver
provided by Microsoft.

Getting Desperate!

Dazed and confused

Reading Sybase Tables Using VB Code
Hi,

I have a problem somthing like....Once i select the Sybase database file using common dialogue controle..i have to papulate the list of tables. once i select the table from the list i need to populate the table structure(field names, data types, sizes) and data of the table.

Pls. help me how to read the Sybase database file and populate the tablenames.

Thanks
prasad

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,

Reading Names Of Tables Stored On A Database.
Hi, I've wrote an application which stores data on a SQL Server. I need to port this app to another site on my work and I am writing a little program that generates the table structure needed on the new SQL Server using DDL. The problem is I don't have any idea on how to know if the tables that my program generates are already created on the server.

There is any way (using SQL) to read the existing table names on a determined database?

Thanks in advance.

Upgrading To VB.NET, 2 Questions (reading Files, Tables)
I've recently upgraded an application from VB6 to VB.NET, and am in the process of fixing the issues that have cropped up as a result. Two main questions I have here, any help would be appreciated.

1. This code snippet in VB6:

Code:
Open d$ For Random Shared As f Len = 256
Get f, 1, X
If X > 0 Then Get f, X + 1, aldata
Close f

translates (according to the upgrade wizard) to:

Code:
FileOpen(f, d, OpenMode.Random, , OpenShare.Shared, 256)
FileGet(f, X, 1)
If X > 0 Then FileGet(f, aldata, X)
FileClose(f)

but this returns a completely different array of values for aldata. I know that d (the filename) and X (the index) are the same in both cases, but the data produced is very dis-similar.

Aldata is a defined structure:

Code:
Structure Allocations
<VBFixedArray(6, 10)> Dim Asset(,) As Integer
End Structure

The numbers are clearly similar, but I can't find a steady correlation between their positions, and it doesn't make sense that they are different at all. Also, there is an 8 (9th column) in the VB6 array that does not appear at all in the VB.NET array.

VB.NET

Code:
0 0 0 0 0 0 0 0 2 0
0 0 0 0 0 0 0 38 0 0
0 0 0 0 0 0 0 0 21.5 0
0 0 0 0 0 0 0 0 9 0
0 0 0 0 0 0 0 0 21.5 0
0 0 0 0 0 0 0 0 0 0

VB6

Code:
2 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 0 0
21.5 0 0 0 0 0 0 0 8 0
9 0 0 0 0 0 0 0 0 0
21.5 0 0 0 0 0 0 0 38 0
0 0 0 0 0 0 0 0 0 0

I need to know what has changed that makes this file access different.

If you need more information, please ask, I am pretty desperate to get this sorted out

2. Much shorter and easier question: The application used several tables in VB6, which have translated to AxMSFlexGrid tables in VB.NET. These tables are not editable, and appear to have no flag to enable editing. Is there an equivalent table I can use that has editing capability without having to use any database add-ins?

Any ideas about any of this would be much appreciated.

Thanks for any help,

Vulpes

Reading Flat Files Into Access Tables
i am trying to read a flat sequential file and transfer the data into an access database table using vb.net.

i am having problems reading the file data directly into a dataset. can this be done directly?

or do i need to use some type of "dataset linked vb object" (like a DataGrid maybe) which i can fill with the file data first?

thnx much

Edited by - lord shmoo on 5/1/2005 12:43:19 PM

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.

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"