PK auto increment on sgdb access

**Branco Medeiros** · Mar 12 '07, 09:45 PM

Re: PK auto increment on sgdb access

JO wrote:

how could i make to know if my column PK on access is auto increment.
>
with .net i use
Dim dtLstKP As DataTable =
MyConn.GetOleDb SchemaTable(Ole DbSchemaGuid.Pr imary_Keys, New Object()
{Nothing, Nothing, strNomTable})

<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), _
ConnectionStrin g, _
ADODB.CursorTyp eEnum.adOpenSta tic, _
ADODB.LockTypeE num.adLockReadO nly)

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

R.Close()
System.Runtime. InteropServices .Marshal.Releas eComObject(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).

**RobinS** · Mar 13 '07, 06:45 AM

Re: PK auto increment on sgdb access

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(M y.Settings.DBCo nnString)
'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(SQLS tring, cn)
Dim rdr As SqlDataReader = _
cmd.ExecuteRead er(CommandBehav ior.KeyInfo)
Dim tbl As DataTable = rdr.GetSchemaTa ble
'Uncomment this to see all of the info
' you can access about each column.
'For Each col As DataColumn In tbl.Columns
' Debug.Print("co l name = " & col.ColumnName & _
' ", type = " & col.DataType.To String)
'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" <nospam@nospam. frwrote in message
news:mn.62db7d7 3b285df48.62161 @nospam.fr...

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.GetOleDb SchemaTable(Ole DbSchemaGuid.Pr imary_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.GetOleDb SchemaTable(Ole DbSchemaGuid.Pr imary_Keys, New Object()
{Nothing, Nothing, strNomTable})
>
>
merci
>
>

JO · Mar 13 '07, 07:45 AM

Re: PK auto increment on sgdb access

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(M y.Settings.DBCo nnString)
'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(SQLS tring, cn)
Dim rdr As SqlDataReader = _
cmd.ExecuteRead er(CommandBehav ior.KeyInfo)
Dim tbl As DataTable = rdr.GetSchemaTa ble
'Uncomment this to see all of the info
' you can access about each column.
'For Each col As DataColumn In tbl.Columns
' Debug.Print("co l name = " & col.ColumnName & _
' ", type = " & col.DataType.To String)
'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" <nospam@nospam. frwrote in message
news:mn.62db7d7 3b285df48.62161 @nospam.fr...

>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.GetOleD bSchemaTable(Ol eDbSchemaGuid.P rimary_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.GetOleD bSchemaTable(Ol eDbSchemaGuid.P rimary_Keys, New Object()
>{Nothing, Nothing, strNomTable})
>>
>>
>merci
>>
>>

thx you this is the good solve +a

**RobinS** · Mar 13 '07, 05:15 PM

Re: PK auto increment on sgdb access

"JO" <nospam@nospam. frwrote in message
news:mn.6a007d7 34c6e1dd7.62161 @nospam.fr...

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
>OLEDBConnectio n and SQLCommand to OLEDBCommand and SqlDataReader to
>OLEDBDataReade r, this will work against Access. But I haven't tried it.
>>
>Dim cn As New SqlConnection(M y.Settings.DBCo nnString)
>'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(SQLS tring, cn)
> Dim rdr As SqlDataReader = _
> cmd.ExecuteRead er(CommandBehav ior.KeyInfo)
> Dim tbl As DataTable = rdr.GetSchemaTa ble
> 'Uncomment this to see all of the info
> ' you can access about each column.
> 'For Each col As DataColumn In tbl.Columns
> ' Debug.Print("co l name = " & col.ColumnName & _
> ' ", type = " & col.DataType.To String)
> '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" <nospam@nospam. frwrote in message
>news:mn.62db7d 73b285df48.6216 1@nospam.fr...

>>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.GetOle DbSchemaTable(O leDbSchemaGuid. 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.GetOle DbSchemaTable(O leDbSchemaGuid. Primary_Keys, New Object()
>>{Nothing, Nothing, strNomTable})
>>>
>>>
>>merci
>>>
>>>

>
thx you this is the good solve +a
>
>

Good, I'm glad it worked.

Robin S.

PK auto increment on sgdb access

PK auto increment on sgdb access

Comment

Comment

Comment

Comment