Changeseed function (kb 287756) stopped working after split DB

[wineguy via AccessMonster.com]

Running Access 2007, lastest xp.

Used the changeseed function from ms kb 287756 to reset autonumber field
which was working until database was split. Now receive 'invalid argument'
from line col.Properties("Seed") = lngSeed.

Anyone else had this problem?

Thanks,

glynn

 

[Chris O'C via AccessMonster.com]

You can't change properties on a linked table. Change the ADODB connection
from the front end to the back end and it'll work.

Chris

 

[wineguy via AccessMonster.com]

Relatively new to Access, is there a property of ADODB to set it to backend?

Thanks,

Glynn

You can't change properties on a linked table. Change the ADODB connection
from the front end to the back end and it'll work.

Chris

Running Access 2007, lastest xp.

[quoted text clipped - 3 lines]

Anyone else had this problem?

 

[Chris O'C via AccessMonster.com]

ADODB is a library for generic data access. Libraries don't have properties.
They have classes, members and enum types. Members can have properties.

If you don't use CurrentProject.Connection you need to set up your connection
string to include the path to the back end db and name the data provider.

Since you're new to developing dbs, the procs below would be easier to learn
from and you could reuse the code for other procs. Call the chgseed proc to
change the seed with testchgseed (after you change the values to match the
path to your back end file, table name etc.)

Public Sub testchgseed()
On Error GoTo ProcErr

Dim strDb As String
Dim strTable As String
Dim strCol As String
Dim lngSeed As Long

strDb = "c:\dbs\db_be.mdb" 'path to be
strTable = "tablename"
strCol = "id" 'autonumber column
lngSeed = 583 'next no.
Call chgseed(strDb, strTable, strCol, lngSeed)
ProcExit:
Exit Sub
ProcErr:
Debug.Print Err.Number & ":" & Err.Description
Resume ProcExit
End Sub


Public Sub chgseed(strDbPath As String, strTable As String, strCol As String,
lngSeed As Long)
On Error GoTo ProcErr

Dim conn As ADODB.Connection
Dim strConnect As String

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDbPath 'connection string
Set conn = New ADODB.Connection
conn.Open strConnect
conn.Execute "ALTER TABLE " & strTable _
& " ALTER COLUMN " & strCol _
& " COUNTER (" & lngSeed & ");"
ProcExit:
conn.Close 'close conn when not needed anymore
Set conn = Nothing 'release memory
Exit Sub
ProcErr:
If Err.Number = -2147467259 Then
Debug.Print "The database file cannot be located.", _
vbCritical, strDbPath
Exit Sub
Else
Debug.Print Err.Number & ":" & Err.Description
Resume ProcExit
End If
End Sub


Chris

 

[wineguy via AccessMonster.com]

ADODB is a library for generic data access. Libraries don't have properties.
They have classes, members and enum types. Members can have properties.

If you don't use CurrentProject.Connection you need to set up your connection
string to include the path to the back end db and name the data provider.

Since you're new to developing dbs, the procs below would be easier to learn
from and you could reuse the code for other procs. Call the chgseed proc to
change the seed with testchgseed (after you change the values to match the
path to your back end file, table name etc.)

Public Sub testchgseed()
On Error GoTo ProcErr

Dim strDb As String
Dim strTable As String
Dim strCol As String
Dim lngSeed As Long

strDb = "c:\dbs\db_be.mdb" 'path to be
strTable = "tablename"
strCol = "id" 'autonumber column
lngSeed = 583 'next no.
Call chgseed(strDb, strTable, strCol, lngSeed)
ProcExit:
Exit Sub
ProcErr:
Debug.Print Err.Number & ":" & Err.Description
Resume ProcExit
End Sub

Public Sub chgseed(strDbPath As String, strTable As String, strCol As String,
lngSeed As Long)
On Error GoTo ProcErr

Dim conn As ADODB.Connection
Dim strConnect As String

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDbPath 'connection string
Set conn = New ADODB.Connection
conn.Open strConnect
conn.Execute "ALTER TABLE " & strTable _
& " ALTER COLUMN " & strCol _
& " COUNTER (" & lngSeed & ");"
ProcExit:
conn.Close 'close conn when not needed anymore
Set conn = Nothing 'release memory
Exit Sub
ProcErr:
If Err.Number = -2147467259 Then
Debug.Print "The database file cannot be located.", _
vbCritical, strDbPath
Exit Sub
Else
Debug.Print Err.Number & ":" & Err.Description
Resume ProcExit
End If
End Sub

Chris

Relatively new to Access, is there a property of ADODB to set it to backend?

[quoted text clipped - 4 lines]

Hi Chris,

Appreciate your time and input... I tried your code but encountered a problem.
.. it could not find an Access 2007 file (.accdb).

I had an old version of my database (2002) with an .mdb extension and your
code worked fine...

any ideas on what could be the problem?

thanks in advance,

Glynn

 

[Chris O'C via AccessMonster.com]

The code I gave you is for an mdb file. It won't work on an accdb file.
Accdb files need a different data provider (because you're using the ACE db
engine, not Jet) in the connection string.

I can give you different code but I can't test it for you. You're on your
own if it doesn't work but you could always convert the accdb to an mdb file
and use my first example.

In the testchgseed proc change this line:

strDb = "c:\dbs\db_be.mdb" 'path to be

to this:

strDb = "c:\dbs\db_be.accdb" 'path to be

In the chgseed proc change this line:

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDbPath 'connection string

to this:

strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & strDbPath 'connection string


Chris

 

[wineguy via AccessMonster.com]

Hi Chris,

Worked like a charm!!!

Thanks again,

Glynn

The code I gave you is for an mdb file. It won't work on an accdb file.
Accdb files need a different data provider (because you're using the ACE db
engine, not Jet) in the connection string.

I can give you different code but I can't test it for you. You're on your
own if it doesn't work but you could always convert the accdb to an mdb file
and use my first example.

In the testchgseed proc change this line:

strDb = "c:\dbs\db_be.mdb" 'path to be

to this:

strDb = "c:\dbs\db_be.accdb" 'path to be

In the chgseed proc change this line:

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDbPath 'connection string

to this:

strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & strDbPath 'connection string

Chris

Appreciate your time and input... I tried your code but encountered a problem.
.. it could not find an Access 2007 file (.accdb).

[quoted text clipped - 3 lines]

any ideas on what could be the problem?