Please Help!! Error 3111

[StuJol]

I have an Access2003 database split into a FE & BE. depending on the frontend
version, it updates / creates new tables in the backend using DAO. All has
worked fine in the past until just recent where i started to get error 3111.
sometimes the DAO code would run without this error and some times i got this
error. now this error appears all the time when trying to create / modify BE
tables. The exact error is Error 3111, could not create, no modify design
permission for table or quey "tblTable"

i have approx 15 different functions that run DAO and none of them will
work, even thou they once did so i dont think its a code issue.

i have uninstalled Access2003 and re-installed and that made no difference.
i have ran virus scans and found nothing. i have bought registry mechanic and
also RegCur but even thou they have fixed 100's of issues, i still continue
to get this 3111 Error.

below is an example of one of my function. if anyone has any ideas please
please let me know.

Function Step0() As Boolean
On Error GoTo Err_Step0

'Action: Creating tblUpgradeEventLog

'If table already exists, cancel operation and move to next step.
If fExistTable2("tblUpgradeEventLog") = True Then
Step1a 'Run Function
Exit Function
End If

'Create table tblHMRCIncomeCategorys using DAO.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field


Set db = OpenDatabase("C:\Program Files\Easara\Easara Home Office
Suite\Database\Home Office Suite Database.mdb")
Set tdf = db.CreateTableDef("tblUpgradeEventLog")


'Specify the fields.
With tdf

'AutoNumber: Long with the attribute set.
Set fld = .CreateField("IDnumber", dbLong)
fld.Attributes = dbAutoIncrField + dbFixedField
.Fields.Append fld

.Fields.Append .CreateField("EventDate", dbDate)
.Fields.Append .CreateField("EventNumber", dbText)
.Fields.Append .CreateField("EventDescription", dbText, 255)
.Fields.Append .CreateField("CallingProc", dbText)
.Fields.Append .CreateField("UserName", dbText)
.Fields.Append .CreateField("ShowUser", dbBoolean)

End With

'Save table.
db.TableDefs.Append tdf
Set fld = Nothing
Set tdf = Nothing

'Clean up
Application.RefreshDatabaseWindow 'Show the changes
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

'Proceed with upgrade
Step1a ' Run Function

Exit_Step0:
Exit Function

Err_Step0:
Select Case Err.Number
Case Else
Call LogError(Err.Number, Err.Description, "713-002", True)
Resume Exit_Step0
End Select
End Function

 

[pietlinden]

Okay, stupid question - but why do you need to keep recreating the
same table over and over? Could you not just export it from another
database to this one?

 

[StuJol]

i only create the table once. like you said why keep re-creating it?

 

[Peter Hibbs]

StuJol,

Don't know but the Back End Update Utility code at :-
http://www.rogersaccesslibrary.com/...p?FID=21&SID=5debzz2c87c6957859f13b1d2zcccfb4
may give you some ideas as it does the same thing.

HTH

Peter Hibbs.

 

[Peter Hibbs]

StuJol,

I tried your code and it works fine. Of course, I had to Rem out the
lines with the fExistTable2 and Step1a function calls since you did
not provide those functions but the rest of it is OK.

The table was created in the BE correctly although it does not show up
in the FE database window because you have not provided any code to
relink the tables, perhaps you normally do that manually.

Perhaps the problem is in the BE if you cannot make any changes to it
with VBA code.

Peter Hibbs.

 

[StuJol]

Peter,

Many thanks for your time. Yes the code does work well. When i create a new
version of the FE, the links are included to the new tables in the BE.

I've created a new blank BE database and my FE works fine. It creates the
new tables. I have a few different existing BE versions but my FE seems to
reject them all. Why would all my different versions of BE's suddenly become
incomatable with my FE?

 

[Peter Hibbs]

It's difficult to say, it sounds to me like the FE may be corrupted in
some way. I suggest you have a look at these sites and try out their
suggestions. The Decompile option usually works for me.

Tony Toews has this :-
http://www.granite.ab.ca/Access/decompile.htm

http://www.granite.ab.ca/access/corruptmdbs.htm

Sorry I can't be of more help.

Peter Hibbs.
--

 

[Tony Toews [MVP]]

until just recent where i started to get error 3111.

Couldn't create; no modify design permission for table or query

Set db = OpenDatabase("C:\Program Files\Easara\Easara Home Office
Suite\Database\Home Office Suite Database.mdb")

Regular users will have read only permissions to Program Files folder.
Instead you should be use the APPs folder for all such FE and BE MDBs.
That is "C:\Documents and Settings\<user name>\Application Data\Your
App Name" There is an API call to fetch the Application Data folder
name. See the section mentioning CSIDL_APPDATA at
API: Retrieving a Special Folder's location
http://www.mvps.org/access/api/api0054.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/