Improve company productivity with a Business Account.Sign Up

x
?
Solved

rename table column names runtime error

Posted on 2010-09-06
6
Medium Priority
?
482 Views
Last Modified: 2013-11-27
on this line
con.open currentproject.accessconnection

I get runtime error '-2147467259 (80004005)':

the database has been placed in a state by user 'admin' on machine
'fuj1' that prevents it from being opened or locked.




Private Sub Command6_Click()

   'Create a Catalog object
   
   Dim con As New ADODB.Connection
   con.Open CurrentProject.AccessConnection
   
   Dim Cat As New ADOX.Catalog
   Cat.ActiveConnection = con
 
 Dim oldn As String
 Dim newn As String
 Dim tbln As String
 
tbln = "test"
oldn = "dog101"
newn = "dog"

   'Create a table object
   
   Dim Tbl As ADOX.Table
   Set Tbl = New ADOX.Table
  
   Set Tbl = Cat.Tables(tbln)
   Tbl.Columns(oldn).Name = newn
 
   Set Cat = Nothing
   Set Tbl = Nothing


End Sub

Open in new window

0
Comment
Question by:slothnet
6 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33613965
how about using dao to change it eg

 CurrentDb.TableDefs(tbln).Fields(oldn).Name = newn
0
 
LVL 3

Expert Comment

by:fabalou
ID: 33614027
Check that there is no pending saves to be done to the schema before that code runs. If there is anything that needs to be saved (code edits), form edits e.t.c it can all produce that error.
0
 

Author Comment

by:slothnet
ID: 33614064
I restarted the database and now I get run-time error 3251

object or provider is not capable of performing requested operation
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 

Author Comment

by:slothnet
ID: 33614066
on this line

  Tbl.Columns(oldn).Name = newn
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 33614083


test thiscodes

Dim oldn As String
Dim newn As String
Dim tbln As String
Dim tbl As ADOX.Table
Dim cat As New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
 
tbln = "test"
oldn = "dog101"
newn = "dog"
cat.Tables(tbln).Columns(oldn).Name = newn
 
   Set cat = Nothing
   Set tbl = Nothing
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33614097
and how did you define tbl? I showed you a dao example so tbl as to be defined as dao.tabledef

the code I gave is not compatible with using adox
0

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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.

Join & Write a Comment

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

606 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