Key Fields
-
To: Anybody who can help!!
Private mdb as database
Private Function KeyFields() As IndexFields
Dim iIndex As Integer
Dim fld As Field
Set KeyFields = Nothing
With mdb.TableDefs(mTable)
For iIndex = 0 To .Indexes.count - 1
If .Indexes(iIndex).Primary Then
Set KeyFields = .Indexes(iIndex).Fields
Exit For
End If
Next iIndex
End With
End Function
As refer to the sample code attached above, does anybody knows how to translate the code from DAO to ADO. For your information, I'm using SQL Server 7 which was previously upsized from Microsoft Access 97 database on Visual Basic 6 application.
And there's another problem here regarding to SQL statement.
update table1 A inner join table2 B on A.a1=B.a2
Set b1=B.b2
c1=B.c2
d1=B.d2
Is there any problem with the SQL statement attached above? the sql queries said there's error near A in line 1.
Please help if you do know the solutions.
Thanks and regards.
-
The first request for an ado function has been addressed in one of the other forums.
The second one should be:
UPDATE table1
SET b1 = B.b1,
c1 = B.c1,
d1 = B.c1
FROM table1 A, table2 B
WHERE A.a1 = B.a1
**ASCII stupid question, get a stupid ANSI !!!** -
Thanks Sushila.
But there's some more queries here.
Here is the code. Please note the data type declared. Note of the line "Set colKeyFields = KeyFields". The keyFields here is the continuous of the code posted earlier on.
--------------------------------------------------------------
Private mcolFields As New Collection
Private Function SelectQuery() As String
Dim strsql As String
Dim intIndex As Integer
Dim colKeyFields As IndexFields
Set colKeyFields = KeyFields
If Not colKeyFields Is Nothing Then
For intIndex = 0 To colKeyFields.count - 1
If intIndex = 0 Then
...
Else
...
End If
With colKeyFields(intIndex)
Select Case mcolFields(.Name).DataType
Case dbText
Case dbNumeric
Case dbDate
Case Else
End Select
End With
Next intIndex
End If
Let SelectQuery = strsql
End Function
----------------------------------------------------------------
Please help!!
-
Ling Ming - can you please describe briefly what you want to accomplish!
**ASCII stupid question, get a stupid ANSI !!!** -
1) For the VB code, you need to reference ADODB (e.g. Microsoft ActiveX Data Objects 2.7 Library) and ADOX (e.g. Microsoft ADO Ext 2.7 for DDL and Security). Here is the code:
Private Function KeyFields(ByVal mTable As String) As ADOX.Columns
Dim con As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table
Dim idx As ADOX.Index
Set KeyFields1 = Nothing
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myDatabase.mdb;"
Set cat.ActiveConnection = con
With tbl
.Name = mTable
Set .ParentCatalog = cat
End With
For Each idx In tbl.Indexes
If idx.PrimaryKey = True Then
Set KeyFields = idx.Columns
Exit For
End If
Next idx
Set cat = Nothing
con.Close
Set con = Nothing
End Function
Private Function SelectQuery() As String
Dim strSQL As String
Dim intIndex As Integer
Dim cols As ADOX.Columns
Set cols = KeyFields("myTableName")
If Not cols Is Nothing Then
For intIndex = 0 To cols.Count - 1
If intIndex = 0 Then
'...
Else
'...
End If
With cols(intIndex)
Select Case .Type
Case adVarChar
Case adVarNumeric
Case adDBDate
Case Else
End Select
End With
Next intIndex
End If
Let SelectQuery = strSQL
End Function
2) Here is the correct SQL query syntax:
UPDATE table1
SET b1 = B.b2, c1 = B.c2, d1 = B.d2
FROM table1 A INNER JOIN table2 B ON A.a1 = B.a1
-
Thanks JLSSCH and Sushila.
I will try it out and get back to you all when there more problems. But before that can you all help me with this.
How are we going to include SQL statement for crystal report with SQL server? or more generally, how are we going to link vb with crystal report, at the same time extract data from the SQL Server (of course, right? if not, how do we know what data should be displayed in the report?)
Is there any simplest and easy way to do so?
Thanks to anybody who can help!
-
Hello,
The debugger stop at this line, even after I tried many tables with or without Primary Key. The index is equal to nothing.
For Each idx In tbl.Indexes
If idx.PrimaryKey = True Then
Set KeyFields = idx.Columns
Exit For
End If
Next idx
The two functions actually work around with indexes. The KeyFields function is used to get the index of the primary key(PK). Then it was passed to SelectQuery function. In this SelectQuery function, I need to check what is the data type of the PK, and all others fields in the table. That's why we see case statements there. At the end of the function, we need to return a correct SQL statement for further processing.
Is there a way for us to retain the global variable "Private mcolFields As New Collection" which is declared as Collection, as I need to refer back to this same variable in others function depending on the results return. (This is a conversion from DAO to ADO!)
By the way, is these lines of coding correct? They should work correctly, right?
----------------------------------------------------------------
Dim con as new ADODB.connection
Dim rs as new ADODB.Recordset
Dim strsql as string
With con
.provider "MSDASQL"
.connectionstring "..."
.open
End with
strsql="..."
rs.open strsql, con
----------------------------------------------------------------
Thanks to anybody who can help.
-
Sorry, making a correction here.
Does the coding below contains any errors?
----------------------------------------------------------------
Dim con as new ADODB.connection
Dim rs as new ADODB.Recordset
Dim strsql as string
With con
.Provider = "MSDASQL"
.ConnectionString = "driver={SQL Server};server=ServerName;uid=..;pwd =" & gPassword & ";database=" & strDbName
.Open "..", "..", "..."
End With
strsql="..."
rs.open strsql, con
----------------------------------------------------------------
Thanks.
-
Ling Ming:
What error message are you getting from the debugger on the line:
For Each idx in tbl.Indexes
Also, can you see other properties for tbl in the Immediate window (e.g. tbl.Name, etc.), and does tbl in the Immediate window have Intellisense? If not, then tbl has not been initialized properly, and I must have made a mistake so you'll have to check MSDN for help on ADOX.
--Jeff
-
Actually the debugger does not pass through the line. It was like treating it as invisible. However, if we ammend the line like this, the debugger will continue with the If statement in the For loop. But we notice that the return values for the function is as columns, which is not correct.
The original code play around the 2 functions with indexes to get the name, data type, and to determine whether it's a Primary Key for the field in the table. Herewith I attached the original code again which actually the same with what I'd attached above.
I got and error which sounds like this:
[MICROSOFT][ODBC SQL SERVER DRIVER][SQL SERVER]Violation of PRIMARY KEY constraints '..._PK'. Cannot insert duplicate key in object 'Table_name'.
Really appreaciate if you tell me the solution if you do know about it. Thanks a lot.
-
Forgot the attachment of codes
Private Function SelectQuery() As String
Dim strsql As String
Dim iIndex As Integer
Dim colKeyFields As IndexFields
strsql = "SELECT * FROM [" & mstrTable & "]"
Set colKeyFields = KeyFields
If Not colKeyFields Is Nothing Then
For iIndex = 0 To colKeyFields.count - 1
If iIndex = 0 Then
strsql = strsql & " WHERE "
Else
strsql = strsql & " AND "
End If
With colKeyFields(inndex)
Select Case mcolFields(.Name).DataType
Case dbText
Case dbNumeric
Case dbDate
Case Else
End Select
End With
Next iIndex
End If
Let SelectQuery = strsql
End Function
Private Function KeyFields() As IndexFields
Dim iIndex As Integer
Set KeyFields = Nothing
With mdbDatabase.TableDefs(mstrTable)
For iIndex = 0 To .Indexes.count - 1
If .Indexes(iIndex).Primary Then
Set KeyFields = .Indexes(iIndex).Fields
Exit For
End If
Next iIndex
End With
End Function
--------------------------------------------------------------------------
Variables declared as global variable
Private mcolFields As New Collection
Private mdb as database
Thanks
-
ling ming - am addressing ONLY the error portion...
"[MICROSOFT][ODBC SQL SERVER DRIVER][SQL SERVER]Violation of PRIMARY KEY constraints '..._PK'. Cannot insert duplicate key in object 'Table_name'"
Somewhere a piece of code is trying to insert a value in a primary key field (or maybe it's a trigger set on one of the tables referenced in the code) - & this value is a duplicate - since PK fields have to be unique, it's throwing up this error.
**ASCII stupid question, get a stupid ANSI !!!** -
You are right, Sushila. Initially, the code fragment below:
For Each idx In tbl.Indexes
If idx.PrimaryKey = True Then
Set KeyFields = idx.Columns
Exit For
End If
Next idx
the debugged result for tbl.Indexes is 0 all the time though tbl was given .name property. However, I tried using:
cat.Tables(mstrTable).Indexes, it gives me some number (i.e. 9) and the code continued with the Set KeyFields = idx.Columns as the idx.PrimaryKey = True. Though I couldn't find out what's idx.columns pass into the Keyfields. And the Keyfields return 9 when I use debugger to query its .count property. The return value of keyfields will be passed to another function as to set cols = keyfields, where the cols declared as ADOX.Columns. When my code continues and come to cols.count, the value return 0 and everything goes messy...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply