PK auto increment on sgdb access

  • Thread starter Thread starter JO
  • Start date Start date
J

JO

hello,
how could i make to know if my column PK on access is auto increment.

with .net i use
Dim dtLstKP As DataTable =
MyConn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, New Object()
{Nothing, Nothing, strNomTable})

thx






bonjour
comment puis je savoir si la cle primaire primaire d'une table access
est auto incrementée ??

en .net j'utilise

Dim dtLstKP As DataTable =
MyConn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, New Object()
{Nothing, Nothing, strNomTable})


merci
 
JO said:
how could i make to know if my column PK on access is auto increment.

with .net i use
Dim dtLstKP As DataTable =
MyConn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, New Object()
{Nothing, Nothing, strNomTable})
Click to expand...
<snip>

I don't think you'll manage to do it using the Access schemata...

You could use ADODB, I guess.

<aircode>
Dim R As New ADODB.Recordset
R.Open( _
String.Format( _
"select {1} from {0} where 1=0", TableName, IDField), _
ConnectionString, _
ADODB.CursorTypeEnum.adOpenStatic, _
ADODB.LockTypeEnum.adLockReadOnly)

Dim IsAutoIncrement As Boolean = _
CType(R(IDField).Properties("IsAutoIncrement").Value, Boolean)

R.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(R)
</aircode>

HTH.

Regards,

Branco.

PS: "aircode" above means that you can't simply copy paste the code;
you'll need to refactor it somehow to suit your needs. It also means
that the code *may* have been typed directly in this response, so
typing errors may happen. It *doesn't* mean that I didn't test the
concept that the code relies on (which I did).
 
Here's one way to get the attributes of the columns in a table. I've done
this with SQLServer. My theory is if you change SqlConnection to
OLEDBConnection and SQLCommand to OLEDBCommand and SqlDataReader to
OLEDBDataReader, this will work against Access. But I haven't tried it.

Dim cn As New SqlConnection(My.Settings.DBConnString)
'put the table name in brackets in case it has spaces in it
Dim SQLString As String = "SELECT * FROM [" & tableName & "]"
Try
cn.Open()
Dim cmd As New SqlCommand(SQLString, cn)
Dim rdr As SqlDataReader = _
cmd.ExecuteReader(CommandBehavior.KeyInfo)
Dim tbl As DataTable = rdr.GetSchemaTable
'Uncomment this to see all of the info
' you can access about each column.
'For Each col As DataColumn In tbl.Columns
' Debug.Print("col name = " & col.ColumnName & _
' ", type = " & col.DataType.ToString)
'Next
For Each row As DataRow In tbl.Rows
Debug.Print("{0}, IsKey = {1}, IsIdentity = {2} ", _
row("ColumnName"), row("IsKey"), row("IsIdentity"))
Next
rdr.Close()
Catch
MessageBox.Show("Error opening the connection to the database.")
Finally
cn.Close()
End Try

I think the IsIdentity value is what you are looking for.

Robin S.
 
Dans son message précédent, RobinS a écrit :
Here's one way to get the attributes of the columns in a table. I've done
this with SQLServer. My theory is if you change SqlConnection to
OLEDBConnection and SQLCommand to OLEDBCommand and SqlDataReader to
OLEDBDataReader, this will work against Access. But I haven't tried it.

Dim cn As New SqlConnection(My.Settings.DBConnString)
'put the table name in brackets in case it has spaces in it
Dim SQLString As String = "SELECT * FROM [" & tableName & "]"
Try
cn.Open()
Dim cmd As New SqlCommand(SQLString, cn)
Dim rdr As SqlDataReader = _
cmd.ExecuteReader(CommandBehavior.KeyInfo)
Dim tbl As DataTable = rdr.GetSchemaTable
'Uncomment this to see all of the info
' you can access about each column.
'For Each col As DataColumn In tbl.Columns
' Debug.Print("col name = " & col.ColumnName & _
' ", type = " & col.DataType.ToString)
'Next
For Each row As DataRow In tbl.Rows
Debug.Print("{0}, IsKey = {1}, IsIdentity = {2} ", _
row("ColumnName"), row("IsKey"), row("IsIdentity"))
Next
rdr.Close()
Catch
MessageBox.Show("Error opening the connection to the database.")
Finally
cn.Close()
End Try

I think the IsIdentity value is what you are looking for.

Robin S.
-------------------------
JO said:
hello,
how could i make to know if my column PK on access is auto increment.

with .net i use
Dim dtLstKP As DataTable =
MyConn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, New Object()
{Nothing, Nothing, strNomTable})

thx






bonjour
comment puis je savoir si la cle primaire primaire d'une table access est
auto incrementée ??

en .net j'utilise

Dim dtLstKP As DataTable =
MyConn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, New Object()
{Nothing, Nothing, strNomTable})


merci
Click to expand...
Click to expand...

thx you this is the good solve +a
 
JO said:
Dans son message précédent, RobinS a écrit :
Here's one way to get the attributes of the columns in a table. I've
done this with SQLServer. My theory is if you change SqlConnection to
OLEDBConnection and SQLCommand to OLEDBCommand and SqlDataReader to
OLEDBDataReader, this will work against Access. But I haven't tried it.

Dim cn As New SqlConnection(My.Settings.DBConnString)
'put the table name in brackets in case it has spaces in it
Dim SQLString As String = "SELECT * FROM [" & tableName & "]"
Try
cn.Open()
Dim cmd As New SqlCommand(SQLString, cn)
Dim rdr As SqlDataReader = _
cmd.ExecuteReader(CommandBehavior.KeyInfo)
Dim tbl As DataTable = rdr.GetSchemaTable
'Uncomment this to see all of the info
' you can access about each column.
'For Each col As DataColumn In tbl.Columns
' Debug.Print("col name = " & col.ColumnName & _
' ", type = " & col.DataType.ToString)
'Next
For Each row As DataRow In tbl.Rows
Debug.Print("{0}, IsKey = {1}, IsIdentity = {2} ", _
row("ColumnName"), row("IsKey"), row("IsIdentity"))
Next
rdr.Close()
Catch
MessageBox.Show("Error opening the connection to the database.")
Finally
cn.Close()
End Try

I think the IsIdentity value is what you are looking for.

Robin S.
-------------------------
JO said:
hello,
how could i make to know if my column PK on access is auto increment.

with .net i use
Dim dtLstKP As DataTable =
MyConn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, New Object()
{Nothing, Nothing, strNomTable})

thx






bonjour
comment puis je savoir si la cle primaire primaire d'une table access
est auto incrementée ??

en .net j'utilise

Dim dtLstKP As DataTable =
MyConn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, New Object()
{Nothing, Nothing, strNomTable})


merci
Click to expand...
Click to expand...

thx you this is the good solve +a
Click to expand...

Good, I'm glad it worked.

Robin S.
 
Back
Top