Hoew to alter the data definition of a linked table

I recently split an Access 2002 database.  The data is in a seperate database and is linked to the other with the code.  The problem is I had a SQL statement that alters one of the tables (DoCmd.RunSQL "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)").  The problem is that now that the database is split in two, I am getting the following error: "3611 Cannot execute data definition statements on linked data sources.".

Any suggestion on how to acheive the alter statement abouve on a lined table?

myfleetmanAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
myfleetman
i tried this code on a backend mdb on the network share and it work,
change CurrentDb  to DB on the execute "Alter....

Dim DB As Database
Dim tbl As TableDef
Set DB = OpenDatabase(Path to db)
          For Each tbl In DB.TableDefs
               If tbl.Name = "tblInvoice" Then
                   Db.Execute "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)"
                   Exit Sub
               End If
          Next tbl

   DB.Close

End Sub
0
 
jadedataMS Access Systems CreatorCommented:
ALTER TABLE [c:\pathtodatabase\databasename.mdb].tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)")

and I would use currentdb.execute for this instead of runsql
0
 
Rey Obrero (Capricorn1)Commented:
you can only modify the original table, not the linked ones.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
peh803Commented:
I don't know of any way to run DDL statements against linked data sources; this is deliberate behavior for security considerations.  I believe you have to connect directly to the data source you wish to alter -- of course, I learn 100 new things every day, and maybe this will fall into today's list.  But...with that caveat, I don't think you can do it.

peh803
0
 
GRayLCommented:
As you are trying to alter a table in another MDB, I think you have to use the In clause:

(DoCmd.RunSQL "ALTER TABLE tblInvoice In "MyDrive:\MyPath\MyMdbName" ALTER COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)").

I assume you have checked the alter column syntax locally and that it works.
0
 
jadedataMS Access Systems CreatorCommented:
setting the count start to an invoice number doesn't sound like its going to work too many times before the system crashes.
0
 
myfleetmanAuthor Commented:
Ok...I tried the two suggestions above with the following results:

Suggestion 1:
DoCmd.RunSQL "ALTER TABLE [c:\pathtodatabase\databasename.mdb].tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)"

Result: 3259 Invalid field data type.

I didn't use the currentdb.execute because I want to stick with ADO and not use DAO.

Suggestion 2:
DoCmd.RunSQL "ALTER TABLE tblInvoice ALTER IN C:\pathtodatabase\databasename.mdb COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)"

Result: 3293 Syntax error in ALTER TABLE statement.


I tried suggest 2 both with and without the double quotes as stated in the suggestion.
0
 
myfleetmanAuthor Commented:
In case anyone is wondering, I used the actual path of the mdb data file in the above stated path descriptions.
0
 
myfleetmanAuthor Commented:
Jadedata,

I am using the autonumber to increment the invoice number.  I delete the data from the table (empty table) and reset the invoice number to what the user enters as a starting point.  This was the easiest way for me to do this (that I could think of) without writing the routine (code) myself.  It is easily done in a few SQL statements.

This all worked until I split the database.
0
 
Rey Obrero (Capricorn1)Commented:
you have to open the db first

Dim DB As Database
Dim tbl As TableDef
Set DB = OpenDatabase(strPath)
          For Each tbl In DB.TableDefs
               If tbl.Name = "tblInvoice" Then
                   CurrentDb.Execute "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)"

               End If
          Next tbl

   DB.Close

End Sub
0
 
Rey Obrero (Capricorn1)Commented:
oops,

Dim DB As Database
Dim tbl As TableDef
Set DB = OpenDatabase(Path to db)
          For Each tbl In DB.TableDefs
               If tbl.Name = "tblInvoice" Then
                   CurrentDb.Execute "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)"
                   Exit Sub
               End If
          Next tbl

   DB.Close

End Sub

0
 
myfleetmanAuthor Commented:
capreicorn1,

Is there an ADO version of your solution?
0
 
GRayLCommented:
As a quoted SQL string your docmd should be:

DoCmd.RunSQL "ALTER TABLE tblInvoice ALTER IN ""C:\pathtodatabase\databasename.mdb"" COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)"

Note the double double quotes.
0
 
myfleetmanAuthor Commented:
GRayl,

Same error as before:

DoCmd.RunSQL "ALTER TABLE tblInvoice ALTER IN ""C:\pathtodatabase\databasename.mdb"" COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)"

Result: 3293 Syntax error in ALTER TABLE statement.
0
 
jadedataMS Access Systems CreatorCommented:
I hate to be a party pooper,....but if the project is in pure msaccess DAO is the native object type...and should therefore be the optimal method.
Nothing prohibits the use of ado and dao in the same project.
Just a thought.

-j-
0
 
myfleetmanAuthor Commented:
Jadedata,

I using MS Access 2002.  I believe the native object since 2000 has been ADO.  What I had to do was include the DAO 3.6 Object library.  I tried capricorn1's suggestion and got the following error:

3611 Cannot execute data definition statements on linked data sources.

0
 
GRayLCommented:
myfleetman: I have been all over this one. You can do an update query in a remote database, but I do not believe you can alter a table in a remote database - at least in Access 2000. I know they are both action queries, but any datadefinition attemp on the remote DB generates an error on my machine - although the error message says its a syntax error.
0
 
myfleetmanAuthor Commented:
GRayL:

So the answers is "You can't do that in MS Access.".  I can live with that.  After all, there's always more than one way to do most anything.  

The only reason I'm doing the ALTER is to start from a user designated Invoice number.  I know I can write code to increment the invoice number, but does anyone know of a way to do it via SQL?
0
 
GRayLCommented:
I stand corrected. I used part of capricorn1's suggestion:

Change the line:

CurrentDb.Execute "ALTER TABLE tblInvoice ALTER COLUMN

To:

DB.Execute "ALTER TABLE tblInvoice ALTER COLUMN

We go throught the trouble of opening the target DB and then execute on the currentdb!  I still am not sure about your alter column syntax, but if it worked before the split, this should let it work remotely.
0
 
myfleetmanAuthor Commented:
GRayL:

I did what you suggested:

DB.Execute "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)"

I got a different error: 3259 Invalid field data type.  And yes, the syntax did work before I split the database.

Original syntax:

DoCmd.RunSQL "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)"

0
 
Rey Obrero (Capricorn1)Commented:
there could be a syntax error here

Db.Execute "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)"

test it first with

Db.Execute "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter Text"  ' to test if column type will be Text

Db.Execute "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter Number"  ' to test if column type will be Number
0
 
myfleetmanAuthor Commented:
capricorn1:

OK...I did what you are suggesting (see earlier post). Below is my code:

    strSQL = "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)"
    Set DB = OpenDatabase("C:\bart4win\Invoicing\Access\BARTEAS_db.mdb")
    For Each tbl In DB.TableDefs
        If tbl.Name = "tblInvoice" Then
            DB.Execute strSQL
        End If
    Next tbl
    DB.Close

I ran it in debug mode and observed the process as it walked throug the database tables, so I know the access is working.
But, as I stated earlier, I received this error: 3259 Invalid field data type.
0
 
Rey Obrero (Capricorn1)Commented:

 -------------------------------------------------------------------------------------vvvvvvvvvvvvv
 strSQL = "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER (" &
vvvvvvvvvvvvvvvvvvvvv        data type ????
 InvoiceNumber & ",1)"
0
 
myfleetmanAuthor Commented:
capricorn1:

I believe "COUNTER" is the data type.  The InvoiceNumberCounter column is defined as autonumber.  What I am attempting to do is reset the beginning number to a user specified number.

I tried you suggestion to use "NUMBER" instead of "COUNTER" and got no error:

strSQL = "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter NUMBER"
    Set DB = OpenDatabase("C:\bart4win\Invoicing\Access\BARTEAS_db.mdb")
    For Each tbl In DB.TableDefs
        If tbl.Name = "tblInvoice" Then
            DB.Execute strSQL
        End If
    Next tbl
    DB.Close

When I execute the above code the InvoiceNumberCounter column changed from "Autonumber" to "Number".
0
 
GRayLCommented:
VB Help - Answer Wizard - Comparison of Data Types - says Counter is  Autoincrement to Jet - Autonumber when designing tables.
0
 
myfleetmanAuthor Commented:
Update:

After running the above code and changing the InvoiceNumberCounter column to "NUMBER", I reran the below code:

strSQL = "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER"
    Set DB = OpenDatabase("C:\bart4win\Invoicing\Access\BARTEAS_db.mdb")
    For Each tbl In DB.TableDefs
        If tbl.Name = "tblInvoice" Then
            DB.Execute strSQL
        End If
    Next tbl
    DB.Close

The result was the InvoiceNumberCounter column changed to an "AUTONUMBER" data type beginning at 1 and incrementing by 1.

So, now we know that COUNTER is a valid data type using this method.  It appears the problem lies with the reset of the begining number and the increment number syntax (COUNTER (" & InvoiceNumber & ",1)").

0
 
myfleetmanAuthor Commented:
I figured out the problem with the COUNTER reset.  I simply removed the space that was between the COUNTER statement and the "(" and it works.  Go figure.

Before: "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER (" & InvoiceNumber & ",1)"
After: "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER(" & InvoiceNumber & ",1)"

Acutal syntax:

strSQL = "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER(" & InvoiceNumber & ",1)"
    Set DB = OpenDatabase("C:\bart4win\Invoicing\Access\BARTEAS_db.mdb")
    For Each tbl In DB.TableDefs
        If tbl.Name = "tblInvoice" Then
            DB.Execute strSQL
            Exit For
        End If
    Next tbl
DB.Close

I'm going to do some testing and see if any other problems arise from this solution.

0
 
GRayLCommented:
capricorn1: Why loop through the tabledefs when running a data definition query.The SQL will find the right table definition without the loop. I think you can shorten the code to:

strSQL = "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER"
    Set DB = OpenDatabase("C:\bart4win\Invoicing\Access\BARTEAS_db.mdb")
    DB.Execute strSQL
    DB.Close

At least it worked for me from the Immediate Pane.

0
 
myfleetmanAuthor Commented:
GRayL:

You're right.  It does work that way.

    strSQL = "ALTER TABLE tblInvoice ALTER COLUMN InvoiceNumberCounter COUNTER(" & InvoiceNumber & ",1)"
    Set DB = OpenDatabase("C:\bart4win\Invoicing\Access\BARTEAS_db.mdb")
    DB.Execute strSQL
    DB.Close
0
 
Rey Obrero (Capricorn1)Commented:
sorry guys,
i just got back!

GRayL,
i just dump those codes for testing purposes. been using those codes format to check for tables in a db.
good thing you noticed that.

0
 
myfleetmanAuthor Commented:
I would like to award points to capricorn1.  Thanks for the help.  I really like this site.

MyFleetman
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.