Retrieve From 2 Tables
I wrote a program that retrieved employee information from a sql table. The new version of software has split that table into two tables and now I must get data from both tables using a common field, employee number. I wrote the original program with an ado control. What would be the best way to access the second table... another ado control or some other way?Bob
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
How To Retrieve Data From Three Tables Within A Database?
As I know you are all programming experts, may I expect any of you to help me out with a VB problem?
I am doing a project, it is all about developing a Supermarket Transaction Control System which consists of Sales / Purchase / Inventory modules.
I created the database in ACCESS with 6 tables ( ProductRecord ; SalesRecord; CustomerRecord; SupplierRecord; PurchaseRecord and UserRecord). And I use VB6.0 to develop my system.
I use ADO as the data link to linkage the database. In each module, I apply a TOOLBAR (toolbar.buttoms.item(array)) to perform ADD / DELETE and SEARCH functions.
Currently, my Customer Record works. It can perform add new record, delete record and search record. (It retrieves data from CustomerRecord table only). In the same way, I created Sales Record which should retrieve data from 3 different tables within the database ( SalesRecord table; ProductRecord table and CustomerRecord), however, my Sales Record canít perform ADD function properly.
The problem is:-
When I add a new record with proper Sales Record ID; Product ID and Customer ID, it produce me three different records with same Sales Record ID. Let say, I have 3 customers record, 3 product records, when I add a new sales record with one of three customer and one of three products, it produces three records with different customers and different products.
I think my coding part for retrieving data from different tables and write to one table. But I donít know how to fix it. Can any of you help me?
Attached is my SalesRecord coding
Can We Retrieve Data To Datagrid From 2 Tables
I need to retrieve data from 2 table at the same time and to display it to a grid.Only the id field is same in both the tables.Rest of the fields r different.
Now I'm using datagrid and I'm not getting data from 2 tables at one stretch.
Please help me with suggessions
Retrieve Structures Of Tables Through VB Coding?
My question is this!
Is there any way to retrieve the structures of all the tables in the database
and write them to a file (Text file or somthing else...) through VB coding,
for trouble-shooting and maintenance purposes?
(I am new to programming)
Retrieve Data From Three Diff Tables
if some one of u can resolve my problem. I have three tables for example
No.1= Jobs (Job Master File)
No.2= Trans (Issue Transaction File)
No.3 = Bills (Billing Transaction File)
My query is that to calculate Total Issued Quantity which is in Trans table and Total Billed Quantity which located in Bills table. When i select in record set, the un expected result comes out. Following is coding for that.
.ActiveConnection = MyDb
.CursorLocation = adUseServer
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.Properties("irowsetidentity") = True
.Open "select e.jobno,sum(t.issueqty) as Issued,sum(b.billedqty) as
Billed " & _
"FROM (enquiry e INNER JOIN trans t ON " & _
"e.jobid = t.jobid) LEFT OUTER JOIN bills b ON " & _
"B.jobid = E.jobid " & _
"WHERE t.manualchallanno > 0 GROUP BY e.jobno"
Expand AllCollapse All
How To Retrieve Data From Multiple Tables?
I need to retrieve the name of the jobs existing in sql server agent and at the same time, their respective schedule.
Using the statement "SELECT name AS JobName FROM sysjobs" will retrieve the names of the jobs and using the statement "SELECT freq_type AS JobSchedule FROM sysjobschedules" will retrieve the schedule type of the respective jobs.
But I need to display the job name and the job schedule type together, executing the appropriate sql statement in SQL Query Analyzer. How do I go about using the SELECT statement for 2 different tables?
The "appropriate" SQL, when executed, should produce:
Job1 /4 /
Job2 / 64 /
Thanks in advance
Edited by - MMC_Newbie on 10/16/2002 11:07:04 PM
Retrieve List Of SQL Instances && Databases && Tables, Etc.. VB.NET 2005
I want to develop a tool for searching text in ALL tables & stored procedures of a given SQL Server 2005 instances / database.
I found how to find all the instances available on my machine using this code:
Dim sqlInstances As Sql.SqlDataSourceEnumerator = Nothing
Dim dtInstances As DataTable = Nothing
sqlInstances = Sql.SqlDataSourceEnumerator.Instance
dtInstances = sqlInstances.GetDataSources
But from there, could anyone help me finding all the database available for a given instance.
And in that database, how to find all tables and stored proc.
Edited by - dbelley_office on 6/2/2007 9:27:31 AM
Retrieve Data From E-mails, And Append The Data To Different Tables
I want to retrieve data from e-mails, and append the data to different tables. The e-mail format is constant; but I have no idea how to progress in this matter.
I realize that I need to point at or read an e-mail, select that e-mail, start a script (visual basic for applications) to select the field data, and append a specific table or a selected table.
I am using Outlook for e-mails, and Access for my database. Basically, it would be best to have an Outlook add-in that is user customizable to select e-mails first, then to select e-mail data to be appended to selected tables in Access.
What are your suggestions to resolving or completing this task? Thank you.
VB, MS Word Macro - Prompt # Of Tables Wanted, Copy + Paste # Of Tables
I have a Microsoft Word document that contains a table with form fields.
I would like it set up so that when the user first opens up the document, Microsoft Word prompts the user "how many additional tables" they want in addition to the one that already exists in the document.
The macro would then select and copy, then paste the table "that many times" underneath.
Unfortunately I don't know much about Visual Basic. On the bright side, I do know how to make macros in Word. Can anyone here help me out?
Sybase Database Tables Combined With Custom Access Tables.
A problem i have is that i have to combine tables from a Sybase database and the ones i make in access, what i want is to be able to make proper Relations between the ODBC and local tables. But i don't know how i can do this, anyone with tips and or urls with explainations would be more than welcome.
Linking Tables Ala Access Get External Data/Link Tables
I have a scenario where I am in a network environment running SQL Server. Normally, we create reports using Access by creating an empty database and then using the Get External Data/Link Tables function to populate it with the SQL data and then manipulate our local database rather than accessing the SQL server directly.
I want to do the same thing using Visual Basic 6. I want to provide the user a blank .mdb file, or just create one during runtime, and then have VB link to the SQL tables automatically without any user intervention. How can I go about doing this?
Tables Catalog Includes Access System Tables
I'm using ADO with the code below to load table names from an Access database into a Listbox. When I get the names using ADOX.Catalog, inaddition to the User tables, I'm getting all the hidden system tables, MSysACEs, MSysModules, etc. How can I gather only User Tables?
Thanks in advance.
rsData.ActiveConnection = strConnect
'Get tables catalog in active connection and load list of tables in Listbox
Set adCat = New ADOX.Catalog
adCat.ActiveConnection = rsData.ActiveConnection
For intTabCount = 0 To adCat.Tables.Count - 1
--If life serves you lemons...
Ask for salt and a bottle of tequila
DataBase With 4 Tables - How Do I Link Tables For Search
New to VB. I'm using VB 6.0
I am building controls to an ADO Data Control for a DataBase with 4 tables. How do I do a search that would pull information from more than one table at a time.
Example: One Database with...
Movie Actor Table
Movie Director Table
Movie Title/Actor Table
Movie Title Table
By selecting a certain Director, How can I pull up all movies directed by the certain director?
I have ID # that can be "compared" between tables, but I'm not sure how. Using a DataGrid to display the search results, how can I do a search that will search two tables???
Movie titles in the MOVIE TITLE TABLE include Director ID # which are also in the MOVIE DIRECTOR TABLE.
Any suggestions would be most appreciated.
MANY MANY THANKS,
Updating SQL Tables With Data From ORACLE Tables
I am tyring to write a stored procedure which will refresh tables on SQL Server with Data from corresponding Tables in ORACLE. The Oracle database is linked via a Linked Server in SQL. I have witten the following SP:
CREATE PROCEDURE dbo.sp_RefreshDDMSTables
DECLARE @strSQLTable varchar(100)
DECLARE @strDDMSTable varchar(100)
DECLARE @strSQL varchar (100)
DECLARE rs CURSOR FOR
SELECT [Name] FROM dbo.sysobjects WHERE left([name],7)='T44DDMS' AND xType='U'
FETCH NEXT FROM rs INTO @strSQLTable
WHILE @@FETCH_STATUS = 0
SET @strSQL = 'TRUNCATE TABLE ' + @strSQLTable
SET @strDDMSTable = substring(@strSQLTable,9,len(@strSQLTable)-8)
SET @strSQL = 'Insert Into ' + @strSQLTable + ' SELECT ' + @strSQLTable + '.* From P48..T44DDMS.' + @strDDMSTable
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
FETCH NEXT FROM rs INTO @strSQLTable
Whe this SP is executed the following message is returned:
Server: Msg 7405, Level 16, State 1, Line 1
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Can anyone help please?
Reading Access Tables Then Writing To SQL Tables
Hi! I want my program to run an infinite loop that reads new data every 5 mins from an Access 97 table, then append it to an SQL Server 2005. I'm still using VB6.
This loop should start automatically in the background when the VB program is started.
I was thinking of putting the code on the Form Load part of the program. Any thoughts on how to code this?
How To Join 6 Tables And Have 2 Tables Query 1 For Same Field??
Sorry that subject isn't clearer but it's kind of strange what I'm trying to do. I have a database table named "tblJob". This table (which gets updated a LOT) holds secondary keys (I think that's what they're called) of the primary keys of other tables. Here's the layout.
custID (number -- from tblCustomer)
empID (number -- from tblEmployee)
locID (number -- from tblLocation)
homeID (number -- from tblHomeArea)
cityID (number -- from tblCity)
cityID (number -- from tblCity)
Basically I want to query all the records from tblJob WHERE the jobStatus = 'Employee Assigned'. But I'm outputting all of the results into a datagrid in the form so that that the user can clearly see which employee is working with which customer, where (street and city), the customers name, etc.
Anyway, here is the query that I've written which SOMEWHAT works:
SELECT tblJob.jobID, tblJob.custID, tblJob.empID, tblJob.locID, tblJob.homeID, tblJob.jobStatus,
tblLocation.locID, tblLocation.locAddr1, tblLocation.cityID,
tblHomeArea.homeID, tblHomeArea.homeAddr1, tblHomeArea.cityID,
tblCustomer.custID, tblCustomer.custFName, tblCustomer.custLName,
FROM (((tblJob INNER JOIN tblLocation ON tblJob.locID = tblLocation.locID)
INNER JOIN tblHomeArea ON tblJob.homeID = tblHomeArea.homeID)
INNER JOIN tblCustomer ON tblJob.custID = tblCustomer.custID)
INNER JOIN tblEmployee ON tblJob.empID = tblEmployee.empID
This code (which I've cleaned up using a text editor for purposes of this post) is being put into the ADODC.Recordsource field. When I run it in Access 2000 (as a query), I get a pop-up box that asks for "tblCity.cityID" and "tblCity.cityName". When I run it in VB 6.0, I get this error:
Quote:No value given for one or more required parameters
I'm pretty sure the problem is that I'm referencing the cityName field in my select but I'm not making that part of my INNER JOIN code.
Here's my problem. How do I run a query (is it another JOIN or a sub-query???) to get the cityName from tblCity where the tblCity.cityID matches tblLocation.cityID AND
get the cityname from tblCity where the tblCity.cityID matches tblHomeArea.cityID?
I've tried just adding in additional inner joins but none of that is working.
Any help with this would be greatly appreciated. I thought I had read somewhere that sub-queries are slower than JOINS but I am so new to JOINS that I can't figure this out and sub-queries are something I've never even touched.
Thank you in advance for any help you can give me.
Edited by - DTFan on 8/22/2003 10:11:30 AM
Access System Tables - Need Columns As Well As Tables
I have one function that works great that compares SQL tables. I am trying to turn that into a second function that will compare Access mdb tables and columns within the tables.
This works for SQL
Code: cmSQL.CommandText = "SELECT * " _
& "FROM INFORMATION_SCHEMA.COLUMNS i " _
& "JOIN SysObjects s ON s.Name = i.Table_Name " _
& "Where s.Type in ('U','V') "
Set rs = cmSQL.Execute
However, there doesn't seem to be a comparable table, besides MSysObjects, in Access (which appears to only hold objects). Is there a spot that holds the columns in all of the tables?
Am I missing something or can I only compare tables and not columns and tables?
"A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila "
Tables And Procedures Of Access Tables
i somehow was able to get the table names of an access xp database thru ado in vb6. the problem is that the returned fields contains stored procedures and queries too. how do i know which is which?
and thanx in advance
How To Find The Right Two Tables Among 29 Tables
My program makes a database with 29 tables containing numbers. The tables are named "Combination 1" - "Combination 29". All tables have the same structure with two fields. The fields are named "Distance" and "Bend". "Distance" is the same in every table.
I need to find the table with the maximum value for "Bend" and the table with the minimum value for "Bend". If the minimum value is 0, I don't need to use it because almost every table contains atleast one zero. But if some table contains negative values I need the table with the lowest one.
I'm working with VB 6.0, MS Access and the connection to the DB is made with a Data object. I'm not so good at this, so some examples or thorough comments could help me.
After the program has found the min and max values I need it to draw a graph with the values of the two tables displayed.
I would be happy getting help finding the right tables to start with...
How do I retrieve the IP address of my computer and display it in a text box?
How To Retrieve
How woould I retrieve a list of which printers the user has installed, disk drives, and fonts into listboxes?
How To Retrieve Url
i am a student of be computers
i wish to know that
"how can i retrieve a url in vb i.e when i click on a link i should get its url in some text box."
if u know please help me
I would like to know how to retrieve a column value from a listbox via a variable name, in my head it would look like List1.GetItem("ItemId"). Or are the values in the listbox just a big jumple of characters that cannot be access'd or broken down into bits?
Retrieve Sms Using Mscomm
Dim dummy As String
Private Sub Command1_Click()
MSComm1.Output = "ati" + vbCr
dummy = DoEvents()
If MSComm1.InBufferCount Then
Text1.Text = Text1.Text + MSComm1.Input
Private Sub Form_Load()
MSComm1.PortOpen = True
i am doing a project, and after reading quite a lot forums about
mscomm, and a lot of coding too, i still can't get a hold on how we actually
receive message from the device. note that i am using a nokia 6610
mobile phone as the modem, therefore i use the at commands i downloaded
from forum nokia (at command set for nokia gsm products, version 1.0).
what i'm trying to do is, retrieve the text "nokia" from the device,
generated from the at command "ati" (similar to at+gmi) which supposed to
request manufacturer identification. it works just fine with the
hyperterminal. i've traced this code, and apparently when it is executing
"mscomm1.input", a "run time error '8020' : error reading comm device"
occurs. it happens all the time =(
any idea about what am i suppose to do? all the properties of mscomm is
default, except for the comm port.
Can anyone give advice on retrieving file from server..
should i use winsock/file system object..
Cant Retrieve Variables
I have a module and 2 forms one form sends data to the module, while the other should then recieve that data from the module. The data is successfully sent to the module (Debug watches prove this) however when I try to retrieve the data on the second form I get 0 values for the integers and "" for the strings:
Public RMemberID As Integer
Public RSurname As String
Public RForename As String
Public RTableNo As Integer
Public RTablerate As Single
Public RTotalCounter As Integer
'*********RECIEVING FORM ***************
Private Sub Form_Load()
Dim RSurname As String
Dim RForename As String
Dim RTotalCost As Single
Dim RTotalMinutes As Integer
Dim RVAT As Single
RTotalMinutes = RTotalCounter 60
RTotalCost = RTotalCounter * RTablerate
RVAT = RTotalCost * 0.175
Picture1.CurrentX = 0
Picture1.CurrentY = 400
Picture1.Print "RECIEPT FOR USE OF TABLE FACILITIES:"
Picture1.Print "Member ID number:"; RMemberID
Picture1.Print "Surname:"; RSurname; " Forename:"; RForename
Picture1.Print "Table Used:" & RTableNo & ""
Picture1.Print "Total Time Used:" & RTotalMinutes & ""
Picture1.Print "Total Cost(incl. VAT): " & RTotalCounter & "seconds X " & RTablerate & " = " & Format(RTotalCost, "£0.00") & ""
Picture1.Print "VAT: " & RVAT & " (Included in price)"
Picture1.Print "Thank you for your custom."
Picture1.Print "E15 Snooker and American Pool Club."
Thank You. Any help is appreciated.
I have previously posted a thread asking how to save attachments of the email I recieve from a specific sender.
But how can I set up outlook to receive automatically my emails and then search my emails to look for the specific user?
Thanks in advance
I'm trying to retrieve a password off of my Word Doc. Here is my basic algorithm:
'number of characters in password
For x = 0 To 15
'number of different combinations possible
For d = 1 To 58 ^ x
'ASCII code to represent characters in alphabet (ie chr(65)="A")
For i = 65 To 123
pass = CreatePass(pass, x, d, i)
newPass = TryPass(pass)
triedPass = newPass
If passfound Then
MsgBox "Pass is: " & triedPass
Basically it is cycling through each letter of the alphabet and if it doesn't find the password, it adds a character and cycles through on that character position. If it doesn't find it then it cycles the previous position and tries the alphabet on the last position again. It's just a normal brute force algorithm.
The problem is that I'm getting an "Overflow" error. I'm not sure where this is coming from. d is the variable that will reach the highest value but I have dimmed it as variant.
It errors after it reaches 4 characters. (I know this from pausing the code and checking where it is at every now and then).
Does anyone else see what it could be?
Here's another question on VBA that i wanna query :
How do i retrieve a row of records using [ID], which is my primary key and [Date] which is one of the fields in the record onto another form?
[View Assessment Search] is my search form.
"View Assessment" is the form which display all the records.
[Assessment Results] is the database i retrieve my data from.
[ID] is one the fields and also a primary key.
[Date of Assessment] is one of the fields too!
Below is my coding:
Dim IC As String
Dim AssmDate As Date
Set dbs = CurrentDb
IC = Forms![View Assessment Search]!AssNRICTextBox.Value
AssmDate = Forms![View Assessment Search]!AssDateTextBox.Value
SQLline1 = "SELECT * FROM [Assessment Results] WHERE [ID]= ' AND [Date of Assessment]= ';"
SQLline = SQLline1 + IC + "'" + AssmDate + "';"
Set rst = dbs.OpenRecordset(SQLline)
If (rst.RecordCount >=1) Then
stDocName = "View Assessment"
stLinkCriteria = "[ID]=" & "'" & Me!AssIDTextBox & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit
Thanks a million!
Retrieve Column Name
I have spreadsheets generated by a 3rd party application where both the number orf rows and columns vary significantly on output. I am able to determine the number of rows and the number of columns ( as in integer )used that contain data. I am now looking to obtain the actual column name (e.g. "A", "AD","BF" to use in a range/select statment.
My end goal is to transpose the rows to columns for input into another application.
how can i write vba code to retrieve the month from a cell that holds a date
the problem is that i'm doing a comparison. if the month of the date in row i is p and year is q, where p n q are pre-defined, then i want to copy row i to another sheet. however, running the code below returns an error of Type Mismatch. I assigned P and Q to be integers. so how do i go abt resolving this problem? any kind souls pls help
i = 1
k = 0
While Not IsEmpty(.Cells(i, 1))
k = k + 1
MsgBox "1st try" & p & " " & r
If Month(Cells(i, 4)) = p And Year(Cells(i, 4)) = q Then
.Range("A" & i & ":I" & i).Copy sheetB.Cells(k, 1)
i = i + 1
Retrieve Web Content
I am trying to retrieve web content from this webpage http://www.ensembl.org/index.html
Public Function GetPageHTML(ByVal URL As String) As String
' Retrieves the HTML from the specified URL
Dim objWC As New System.Net.WebClient()
Return New System.Text.UTF8Encoding().GetString(objWC.DownloadData(URL))
The above code works on other webpages but not for the one listed above.
There is an error "System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a receive.
It seems like it can retrieve html webcontent but not xhtml. Any help?
How would I go about retrieving the filesize of any file in KB?
Retrieve Row Index
Good morning all,
I am trying to get the row index of a row, in an MSFlexgrid that has a date that matches the date on a DTPicker. What I have is not working. Can anyone help me to get this code working? No errors just not working. Thanks
Private Sub DTPicker3_Click()
Dim i As Integer
For i = Loggrid(0).FixedRows To Loggrid(0).Rows - 1
If .TextMatrix(i, 0) = Format(Form1.DTPicker3.Value) Then
IDtxt = .TextMatrix(i, 0)
Retrieve Key Value From Registry
I need to retrieve the value of Client Access version. The registry path is HKEY_LOCAL_MACHINESoftwareIBMClient AccessCurrentVersion.
Here is the code:
Dim Settings As Variant
Settings = GetSetting("Client Access", "CurrentVersion", "Version")
The value of the Settings I'm getting is null (the actual value of the version key is 5). Any suggestions why?
Thank you very much
How To Retrieve The IE Path?
I am new to VB. I now need to launch the Internet Explorer from a button. How can retrieve the path to the IE so I don't need to hardcode the path. Thanks,
hello, i have a label in a executable called lblOllie, with the app being called ollie.exe. I am making another project and i need to know how to make (label1) on the second application i am making, become the lblOllie from the first application, i think i might need findwindow / findwindowex / sendmessage but i havent got a clue how to use them! Any help would be appreciated.
How To Retrieve OS Login Name
I am trying to use the OS username to restrict access to certain functions in my code. How do I retrieve the current local logged in username? Any help would be greatly appreciated.
Given a pointer to a UDT obtained via
lngPtr = VarPtr(MyUDT)
how is it possible to retrieve the UDT's TypeName?
Can anybody help me with this?:
all i need to do is retrieve a string which is the username of the person using a partiular computer. Is there some sort of cbConstant that is exactly that, ideas?
Retrieve DSN Password
I need to test a system DSN connection and I have no idea what the password is. Is there a way to retrieve this password from the registery?
I have been asked to set up this DSN on 3 other PCs so that the users can run a custome app to connect to the database.
Retrieve Record From MDB
I am trying to look up a record in a MDB file on a button press. I am trying to allow the user to enter the f_id number on the form and then allow them to press a button. On the button press it retrieves the record where f_id = id in the table and populates the rest of the fields so they can edit the record.
Ok I have the following fields on the form
the MCDB is called: test.cdb
the Table is called: tbl_taginfo
the fields in the table are as follows:
id (which = "f_id" on the form)
customer (which = "f_customer" on the form)
colour (which = "f_colour" on the form)
Height (which = "f_height" on the form)
Length (which = "f_length" on the form)
Width (which = "f_width" on the form)
I have no ideas here on how to do it and I have looked all over the net for examples with no results, can someone give me some idea on how to do this.
Thanks in advance.
DB Retrieve Very Slow
I use a MSDE database.
i make a connection to it when my programm launches and is setting up all the data etc.
when i use my program i want to do a search like this:
Set rstInfo = New ADODB.Recordset
rstInfo.Open "SELECT * FROM Client, Product WHERE Type = 'Client' AND Client.productnr = Product.pnr", ModConnection.SearchConnection, adOpenKeyset, adLockReadOnly
the problem is that it takes 6 !!! seconds to do this query.
I use the serversided option.
the problem is that it uses wireless WPA security as networkconnection.
When i put i wire between it the 6 seconds become 0 second (as in immediate)
What can i do about it?
Is it normal?
How can get more speed? (like is MSDE the problem?)
need some help here i'm getting really frustrated
SQL: Retrieve Month Name
I want to display sales in months like this:
I am using following query:
Select month(SalesDate),year(SalesDate),sum(TotSale) from CustSale
Group By month(salesdate),year(SalesDate) order by year(salesdate) desc;
But it does not displays the Month Name. It just displays Month Number.
I tried to use the Format Function, but it is not working.
I am using VB 6 with Access XP.
How To Retrieve Identity?
I have a identity column,
how i can get the value of this column after insert?
using visual basic and sql server
after many readings, i still have problem understanding about retrieving records..
what i want is, when i enter an ID, it will retrieve records base on the ID and display on textboxes. is there any way i can do it?