Solved

Hoew to alter the data definition of a linked table

Posted on 2004-08-19
31
1,835 Views
Last Modified: 2012-08-13
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?

0
Comment
Question by:myfleetman
  • 14
  • 7
  • 6
  • +2
31 Comments
 
LVL 32

Expert Comment

by:jadedata
ID: 11842115
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11842133
you can only modify the original table, not the linked ones.
0
 
LVL 19

Expert Comment

by:peh803
ID: 11842143
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
 
LVL 44

Expert Comment

by:GRayL
ID: 11842479
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
 
LVL 32

Expert Comment

by:jadedata
ID: 11842731
setting the count start to an invoice number doesn't sound like its going to work too many times before the system crashes.
0
 

Author Comment

by:myfleetman
ID: 11843446
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
 

Author Comment

by:myfleetman
ID: 11843469
In case anyone is wondering, I used the actual path of the mdb data file in the above stated path descriptions.
0
 

Author Comment

by:myfleetman
ID: 11843565
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11843648
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11843661
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
 

Author Comment

by:myfleetman
ID: 11843895
capreicorn1,

Is there an ADO version of your solution?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11843983
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
 

Author Comment

by:myfleetman
ID: 11844095
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
 
LVL 32

Expert Comment

by:jadedata
ID: 11844096
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
 

Author Comment

by:myfleetman
ID: 11844365
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 44

Expert Comment

by:GRayL
ID: 11845167
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
 

Author Comment

by:myfleetman
ID: 11845257
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
 
LVL 44

Expert Comment

by:GRayL
ID: 11845269
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
 

Author Comment

by:myfleetman
ID: 11845474
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 125 total points
ID: 11845500
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11845541
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
 

Author Comment

by:myfleetman
ID: 11845556
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11845628

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

Author Comment

by:myfleetman
ID: 11845756
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
 
LVL 44

Expert Comment

by:GRayL
ID: 11845782
VB Help - Answer Wizard - Comparison of Data Types - says Counter is  Autoincrement to Jet - Autonumber when designing tables.
0
 

Author Comment

by:myfleetman
ID: 11845880
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
 

Author Comment

by:myfleetman
ID: 11846001
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
 
LVL 44

Expert Comment

by:GRayL
ID: 11846059
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
 

Author Comment

by:myfleetman
ID: 11846158
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 11846560
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
 

Author Comment

by:myfleetman
ID: 11860071
I would like to award points to capricorn1.  Thanks for the help.  I really like this site.

MyFleetman
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now