Solved

Hoew to alter the data definition of a linked table

Posted on 2004-08-19
31
1,975 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Dilema in Access 2010 3 37
Combo box question 6 55
SUBFORM on ACCESS 2013 8 35
Access date picker/Monday dates only 3 14
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

734 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