|
|
VB SQL - Fieldname,etc - simple PROBLEM
Having trouble with what should be a ridiculously easy small, brief routine in VB. Any insight, would be really helpful.
All Im trying to do is have VB pull data from two databases, two different ways;
database (1)
Simple table, with entries such as (strucuture of db);
P 101 102 103 104 105
-------------------------------------------------------------------
1 1286 2847 5288 2299 3300
2 3101 3024 3503 3024 2305
3 3016 3074 3058 3029 8310
4 3111 3142 3135 3214 3315
5 3116 3417 3518 2319 9400
The above is the table. It is only a static lookup table, no writing or updating will occur, where column 'P' is the primary key field.
VB generates a number through a different internal process, this number will exist in the table within database (1). I want VB to return the field name the number
is found in, along with the primary key field number. I won't know which field the number will be in, (unless I were to open the database manually and scan
all the fields). The field names in this table are numeric, that is to say in the above table;
Field Name 1 = 101, Field Name 2 = 102, Field Name 3 = 103, Field Name 4 = 104, etc....
So the VB app generates say the number '3214', I want VB to search the entire table for the number 314, returning the fieldname it is found in and the corresponding
primaryukey index number, so the correct answer would be;
3214 = FieldName ; " 104 " and Primarykey Index number = 4
Then I want the values, "104, 4" stored in a variable and stuck in a text box or label.
Thats its for database (1).
So here is what is driving me buggy;
My constraint is I'm using VB 4.0 professional version. Yes I know its old, but due to my current circumstances I am unable to upgrade. The database used is Access MDB, the Jet version, 3.0.
To do the above should be really simple, I've tried all kinds of different approaches. I've poured through the massive VB tips and tricks HLP file, the huge
VB BUGS hlp, the online standard HLP, various textbooks, and I can't find any clear guidelines that I can grok to figure out how to implement this.
I'm probably just missing something obvious, I can be a bit dense and slow sometimes but usually can there by plodding. I've been plodding on this one for a while, and have made some progress but am just simply stuck at the moment.
I've tried using recordsets, the data control, snapshots, dynasets, SQL, etc.... often get really weird big error reports. Checked the VB error dox, and found there was a lot of misleading, erroneous bungles reported on the use and intermixing of all these.... Syntax, methods, properties that are reported to work a certain way, don't.
Ideally, I'd just like to use a recordset, pass through an SQL statement and get the data I want. I've tried using 'findfirst', seek, etc.... I'm open to these instead of SQL if need be, IF they will work. I can't get seek to do what I want.
Talked with a guy I know who's an SQL jockey ( I'm not), he tells me I need to know the fieldname in advance if I want to pull the number back with its field name,
I thought using a Select * (all) would let me just pick the whole table, wanting the routine to just automatically search all fields until it finds the value Im after and then tells me the field name, is this not possible? Do I have to specify each and every field name explicitly to search through, if so that seems a bit frustrating.....
Could someone show me some code that will perform this basic operation, please?
For database (2)
Simple table, with entries such as (strucuture of db);
LOOK
UP
CODE# Text 1 Text 2 Text 3
--------------------------------------------------------------------------------------------
1286 Text Entry 1 Text Entry 2 Text Entry 3
3101 Text Entry 1 Text Entry 2 Text Entry 3
3016 Text Entry 1 Text Entry 2 Text Entry 3
3111 Text Entry 1 Text Entry 2 Text Entry 3
3116 Text Entry 1 Text Entry 2 Text Entry 3
This table has only four fields. Field one is "LOOK UP CODE #", Field two is Text 1, Field three is Text 2, Field four is Text 3
The VB app generates a look up code #. It then pass this number to the database table, the corresponding text entries found
in fields Text 1,Text 2,Text 3, are returned. Each text entry is stored in a different variable, and each variable is displayed to a different
textbox, i.e.
var1 = Text1.Text Entry 1
var2 = Text2.Text Entry 2
var3 = Text3.Text Entry 3
textbox1.text = var1
textbox1.text = var2
textbox1.text = var3
Ideally, where ever SQL can be used in conjunction with a recordset is appealing, since its the fastest, but I am most concerned about functionality, just getting this to work without bursting anymore blood vessels in my head, if using seek or some other approach does the trick Im open to this.
I've included some of the code I've been tweaing with below, but its not very pretty and it doesnt do what I describe above, really.... just shows some of the stuff I've been trying .... included as a reference to give an idea as to where I'm at....
Thanks for any responses.... I know this sounds ridiculous but I've been working on this for quite a while, not making much headway.....
Best regards
P
Private Sub Command8_Click()
Dim MyDatabase As Database
Dim MyRecordset As Recordset, MyField As Field
Dim MySQL As String, I As Integer
Dim MyTable As Recordset
Set MyDatabase = Workspaces(0).OpenDatabase("C:\TEST.MDB")
Set MyRecordset = MyDatabase.OpenRecordset("TABLENAME", dbOpenTable) ' Open table.
MyRecordset.Index = "PrimaryKey" ' Define current index.
MyRecordset.Seek "=", "12" ' Seek record.
If MyRecordset.NoMatch Then
MsgBox "match was not found"
Else
MsgBox "match was found"
End If
For I = 0 To MyRecordset.Fields.Count - 1
Set MyField = MyRecordset.Fields(I)
Debug.Print MyField.Name ' Print field name.
Debug.Print MyField.SourceTable ' Print original table name.
Debug.Print MyField.Value ' Print original table name.
Debug.Print "name of column is ;"; MyField.SourceField ' Print original field name.
MyRecordset.Index = "PrimaryKey"
MyRecordset.MoveNext
Next I
MySQL = "SELECT * FROM ninesv WHERE 3 = 12" 'here, 3,4 represnt field, (column names)
For I = 0 To MyRecordset.Fields.Count - 1
Set MyField = MyRecordset.Fields(I)
Debug.Print MyField.Name ' Print field name.
Debug.Print "FIELD NAME ;"; MyField.Name ' Print field name.
Debug.Print MyField.SourceTable ' Print original table name.
Debug.Print "SourceField ;"; MyField.SourceField ' Print original field name.
Debug.Print MyField.SourceField ' Print original field name.
Next I
End Sub
|
|
|
|
|
|
|
// |
zarkorgon
