rename table column names runtime error

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

slothnetAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:


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
 
rockiroadsCommented:
how about using dao to change it eg

 CurrentDb.TableDefs(tbln).Fields(oldn).Name = newn
0
 
fabalouCommented:
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
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.

 
slothnetAuthor Commented:
I restarted the database and now I get run-time error 3251

object or provider is not capable of performing requested operation
0
 
slothnetAuthor Commented:
on this line

  Tbl.Columns(oldn).Name = newn
0
 
rockiroadsCommented:
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
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.