 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / May 2008How To Rebuild Objects From Text Files?
I've read that one method of repairing a misbehaving database is to save all database objects as text and then rebuild them from the text files. I've used the following code posted by Lyle Fairfield to accomplish the first step: Private Sub SaveObjectsAsText() path = CurrentProject.path & "\ObjectsAsText\" SaveDataAccessPagesAsText SaveFormsAsText SaveReportsAsText SaveModulesAsText MsgBox "All Done Saving Access Objects as Text" End Sub Private Sub SaveDataAccessPagesAsText() Dim FileName As String Dim Name As String Dim DataAccessPage As AccessObject For Each DataAccessPage In CurrentProject.AllDataAccessPages Name = DataAccessPages.Name FileName = path & Name & Format(Now(), "yyyymmddhhnn") & ".txt" SaveAsText acDataAccessPage, Name, FileName Next DataAccessPage MsgBox "All Done Saving Data Access Pages as Text" End Sub Private Sub SaveFormsAsText() Dim FileName As String Dim Name As String Dim Form As AccessObject For Each Form In CurrentProject.AllForms Name = Form.Name FileName = path & Name & Format(Now(), "yyyymmddhhnn") & ".txt" SaveAsText acForm, Name, FileName Next Form MsgBox "All Done Saving Forms as Text" End Sub Private Sub SaveReportsAsText() Dim FileName As String Dim Name As String Dim Report As AccessObject For Each Report In CurrentProject.AllReports Name = Report.Name FileName = path & Name & Format(Now(), "yyyymmddhhnn") & ".txt" SaveAsText acReport, Name, FileName Next Report MsgBox "All Done Saving Reports as Text" End Sub Private Sub SaveModulesAsText() Dim FileName As String Dim Name As String Dim Module As AccessObject For Each Module In CurrentProject.AllModules Name = Module.Name FileName = path & Name & Format(Now(), "yyyymmddhhnn") & ".txt" SaveAsText acModule, Name, FileName Next Module MsgBox "All Done Saving Modules as Text" End Sub How do I then rebuild the database objects from the text files that have been created? > I've read that one method of repairing a misbehaving database is to > save all database objects as text and then rebuild them from the text [quoted text clipped - 64 lines] > How do I then rebuild the database objects from the text files that > have been created? Did I ever post this half-page? The whole pages can be found at: http://www.ffdba.com/downloads/Save_MDB_Objects_As_Text.htm or http://www.ffdba.com/downloads/Save_ADP_Objects_As_Text.htm http://www.ffdba.com/downloads/Save_MDB_Objects_As_Text.dat or http://www.ffdba.com/downloads/Save_ADP_Objects_As_Text.dat for download, Use the whole page or module. > > I've read that one method of repairing a misbehaving database is to > > save all database objects as text and then rebuild them from the text [quoted text clipped - 75 lines] > > Use the whole page or module. LoadFromText is a hidden and undocumented procedure. It may be worthwhile to note that LoadFromText AcObjectType, ObjectName, FilePath overwrites the object named ObjectName with whatever instructions are in FilePath. There is, TTBOMK, NO recovery from this. The old object now belongs to the ages, but not to you. Over many years LoadFromText has never failed me, but if I give it wrong or foolish instructions, it carries them out, without any warning, just as it carries out any other instructions. In summary, the making of backups and/or safe copies may be a worthwhile expenditure of time and resources before using LoadFromText. Coincidentally, I am doing that right now, in an effort to change all "Guidance" objects and references to same, to "StudentSuccess" objects. Ain't politics grand? I expect that StudentSuccess staff will be much more effective than Guidance staff. The first thing I did was to make a safe copy of the ADP (it could have been MDB) file. > > > I've read that one method of repairing a misbehaving database is to > > > save all database objects as text and then rebuild them from the text [quoted text clipped - 95 lines] > The first thing I did was to make a safe copy of the ADP (it could > have been MDB) file. Access and the COM objects that can be exposed through VBA, JET and ADO are marvelously powerful. Those posters here who denigrate them are just plain wrong. This is my preliminary code. I claim it changes everything in my ADP that refers to Guidance, from Guidance to StudentSuccess. Is it perfect? No. For instance there are some Labels and Captions which will have become "StudentSuccess" that I will change interactively to "Student Success" Is it Beta? Not that yet; it's my first whack at this since 1998. But IMO it's worth the couple of hours (since beginning work this morning) spent creating it, because next time any client says, I want to change ALL Matildas to Rosemarys I can do that in 30 seconds. And clients do make such requests, even when they promise that they won't. And if you WANT the NEXT contract, sometimes it's better to say, "Sure, we can have that tomorrow, no problem", instead of, "It's going to cost you MORE, and how does late August sound?". Option Compare Database Option Explicit Private Const OldComponent$ = "Guidance" Private Const NewComponent$ = "StudentSuccess" Private Sub ScanComponent() Dim AccessObject As AccessObject Dim FileNumber% Dim ObjectName$ Dim SQL$ Dim Script$ Dim TempFullPath$ Dim TempPath$ ' change the table name ' ------------------- On Error Resume Next SQL = "sp_rename 'GuidanceStaff', 'StudentSuccessStaff'" CurrentProject.Connection.Execute SQL On Error GoTo 0 ' ------------------- ' change references from OldComponent to NewComponent in Procedures, Views and (SQL) Functions ' ------------------- SQL = "SELECT sc.text" SQL = SQL & " FROM SysComments sc" SQL = SQL & " JOIN SysObjects so" SQL = SQL & " ON sc.ID = so.ID" SQL = SQL & " WHERE so.Name = " For Each AccessObject In CurrentData.AllStoredProcedures ObjectName = AccessObject.Name Script = CurrentProject.Connection.Execute(SQL & "'" & ObjectName & "'")(0) AlterComponent True, ObjectName, Script Next AccessObject For Each AccessObject In CurrentData.AllViews ObjectName = AccessObject.Name Script = CurrentProject.Connection.Execute(SQL & "'" & AccessObject.Name & "'")(0) AlterComponent True, ObjectName, Script Next AccessObject For Each AccessObject In CurrentData.AllFunctions ObjectName = AccessObject.Name Script = CurrentProject.Connection.Execute(SQL & "'" & AccessObject.Name & "'")(0) AlterComponent True, ObjectName, Script Next AccessObject ' ------------------- ' get temp path ' ------------------- TempPath = Environ$("temp") If Len(TempPath) = 0 Then TempPath = CurDir$() ' ------------------- ' change references from OldComponent to NewComponent in Forms, Reports and (SQL) Modules ' and their names ' objects whose names include OldComponent will not be deleted ' ------------------- For Each AccessObject In CurrentProject.AllForms ObjectName = AccessObject.Name SaveAsText acForm, ObjectName, TempPath & "\" & ObjectName Next AccessObject For Each AccessObject In CurrentProject.AllReports ObjectName = AccessObject.Name SaveAsText acReport, ObjectName, TempPath & "\" & ObjectName Next AccessObject For Each AccessObject In CurrentProject.AllModules ObjectName = AccessObject.Name SaveAsText acModule, ObjectName, TempPath & "\" & ObjectName Next AccessObject ' ------------------- ' ------------------- For Each AccessObject In CurrentProject.AllForms ObjectName = AccessObject.Name FileNumber = FreeFile() TempFullPath = TempPath & "\" & ObjectName Open TempFullPath For Binary As #FileNumber Script = String(LOF(FileNumber), vbNullChar) Get #FileNumber, , Script Close #FileNumber Kill TempFullPath If InStr(Script & " " & ObjectName, OldComponent) <> 0 Then ChangeScript False, ObjectName, Script FileNumber = FreeFile Open TempFullPath For Binary As #FileNumber Put #FileNumber, , Script Close #FileNumber LoadFromText acForm, ObjectName, TempFullPath Kill TempFullPath End If Next AccessObject For Each AccessObject In CurrentProject.AllReports ObjectName = AccessObject.Name FileNumber = FreeFile() TempFullPath = TempPath & "\" & ObjectName Open TempFullPath For Binary As #FileNumber Script = String(LOF(FileNumber), vbNullChar) Get #FileNumber, , Script Close #FileNumber Kill TempFullPath If InStr(Script & " " & ObjectName, OldComponent) <> 0 Then ChangeScript False, ObjectName, Script FileNumber = FreeFile Open TempFullPath For Binary As #FileNumber Put #FileNumber, , Script Close #FileNumber LoadFromText acReport, ObjectName, TempFullPath Kill TempFullPath End If Next AccessObject For Each AccessObject In CurrentProject.AllModules ObjectName = AccessObject.Name If ObjectName <> "ChangeComponent" Then FileNumber = FreeFile() TempFullPath = TempPath & "\" & ObjectName Open TempFullPath For Binary As #FileNumber Script = String(LOF(FileNumber), vbNullChar) Get #FileNumber, , Script Close #FileNumber Kill TempFullPath If InStr(Script & " " & ObjectName, OldComponent) <> 0 Then ChangeScript False, ObjectName, Script FileNumber = FreeFile Open TempFullPath For Binary As #FileNumber Put #FileNumber, , Script Close #FileNumber LoadFromText acModule, ObjectName, TempFullPath Kill TempFullPath End If End If Next AccessObject End Sub Private Sub ChangeScript(ByVal SQL As Boolean, ByRef ObjectName$, ByRef Script$) Dim Iterator& If SQL Then DropSQLObject ObjectName Script = Replace(Script, "ALTER", "CREATE") End If For Iterator = 1 To 3 ObjectName = Replace(ObjectName, _ StrConv(OldComponent, Iterator), _ StrConv(NewComponent, Iterator), , vbBinaryCompare) Script = Replace(Script, _ StrConv(OldComponent, Iterator), _ StrConv(NewComponent, Iterator), , vbBinaryCompare) Next Iterator ObjectName = Replace(ObjectName, OldComponent, NewComponent, vbTextCompare) Script = Replace(Script, OldComponent, NewComponent, vbTextCompare) End Sub Private Sub AlterComponent(ByVal SQL As Boolean, ByRef ObjectName$, ByRef Script$) If InStr(Script, OldComponent) <> 0 Then ChangeScript SQL, ObjectName, Script DropSQLObject ObjectName CurrentProject.Connection.Execute Script End If End Sub Public Sub DropSQLObject(ByVal ObjectName$) Dim SQL$ SQL = "IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ObjectName]'))" SQL = SQL & " DROP VIEW [dbo].[ObjectName]" SQL = Replace(SQL, "ObjectName", ObjectName) CurrentProject.Connection.Execute SQL End Sub Yes, I knw it's for an ADP, but i suspect changing ti to work in an MDB is no more than 15 minutes work. > And if you WANT the NEXT contract, sometimes it's better > to say, "Sure, we can have that tomorrow, no problem", instead > of, "It's going to cost you MORE, and how does late > August sound?". But sometimes, Lyle, wouldn't it be satisfying to tell them? :-) Larry > > And if you WANT the NEXT contract, sometimes it's better > > to say, "Sure, we can have that tomorrow, no problem", instead [quoted text clipped - 4 lines] > > Larry I have told too many, Larry; wayyyyyyyyyyyy too many. I'm trying to learn humility in my old age. Hasn't worked yet though. Maybe tomorrow; maybe not. > > But sometimes, Lyle, wouldn't it be satisfying to tell them? :-) > I have told too many, Larry; wayyyyyyyyyyyy too > many. I'm trying to learn humility in my old age. > Hasn't worked yet though. Maybe tomorrow; > maybe not. Hmm. You'd think, after all these years, we'd have earned the right not to be humble. After all, aren't we legends in our own minds? And, wasn't there an old song that went something like this? "There's no tomorrow when you're older'n dirt." I've used the code from the linked page that Lyle has posted above to save and then load as text. The saving bit works fine but then the code fails at: app.LoadFromText acForm, Name, FileName with the error: Runtime error '2285' (Database Name) can't create the output file. Not sure what's causing this. Any help would be appreciated. > I've used the code from the linked page that Lyle has posted above to > save and then load as text. The saving bit works fine but then the [quoted text clipped - 8 lines] > > Not sure what's causing this. Any help would be appreciated. The code, in total, creates text files and a new copy of the database. It uses another instance of the Access application to do so. If you leave some of the code out; it's likely to err. So, first question, did you leave some of the code out? No, I'm using the code in total that I copied from your linked page. It creates the text files and a new database minus the objects in a new folder, but stops with the error at the line of code that I posted when trying to recreate the forms. I don't have any data pages, so the forms are the first thing that it is trying to recreate. > No, I'm using the code in total that I copied from your linked page. > It creates the text files and a new database minus the objects in a > new folder, but stops with the error at the line of code that I posted > when trying to recreate the forms. I don't have any data pages, so the > forms are the first thing that it is trying to recreate. I don't know. A Search for "can't create the output file" in this newsgroup doesn't find anything pertinent; ditto for 2285. But a general Google search for "can't create the output file 2285" returns many hits. A really quick glance at these might make one wonder if the error is related to file attributes or folder permissions. But when you "LoadFromText" you're not creating an "output file", at least not ostensibly (VB/Access might be doing this with a temporary file behind the scenes.) So, where from here? Have you tried "On Error Resume Next" to see if any forms load? You're writing, obviously, to the folder where the (new) app lives. And you must have write permissions there or SaveAsText would fail. That's all that comes to mind on a Sunday morning, but several other people are using the SaveAsText-LoadFromText duality as a cleanser and perhaps someone will recognize the problem and be able to help. > I don't know. A Search for "can't create the output file" in this > newsgroup doesn't find anything pertinent; ditto for 2285. [quoted text clipped - 15 lines] > people are using the SaveAsText-LoadFromText duality as a cleanser and > perhaps someone will recognize the problem and be able to help. Thanks for your help Lyle. I tried using "On Error Resume Next" and the result was interesting. An "Errors.txt" file was created for each form, 37 of them in this case. Each Errors.txt file contains nothing. I thought that Vista might be doing strange things to me and booted into XP but the result was the same. As you say, perhaps someone else will be able to help. > > I don't know. A Search for "can't create the output file" in this > > newsgroup doesn't find anything pertinent; ditto for 2285. [quoted text clipped - 22 lines] > booted into XP but the result was the same. As you say, perhaps > someone else will be able to help. A little further search brings up this error described but no suggestion as to its cause or cure. If you were to post the text file for the first form that fails, some of us might see if we can load the form into a database with the LoadFromText method. If so, we will know its not your form. If not, we might be able to study the problem in general terms, (or you could just send me the text file and I'll post it: lyle dot fairfield at gmail dot com). > A little further search brings up this error described but no > suggestion as to its cause or cure. If you were to post the text file [quoted text clipped - 3 lines] > general terms, (or you could just send me the text file and I'll post > it: lyle dot fairfield at gmail dot com).- Hide quoted text - After a few hours of experimentation I've made a few observations. The DB I am working with is an A2003 database, but I have a sneaking suspicion that it is one that I have converted from A2000. Not sure if this has any bearing on anything but I imported all of the objects into a new A2003 database and tried running the code again. It partially worked this time. As before, all the objects were saved as text, but this time some of the forms were imported from the text files before it fell over. I then tried importing some of the forms that were missed on the import one by one by using the LoadFromText method and this worked. This seems to indicate that there is nothing inherently wrong with the forms. When I view the forms they look and work fine. The scenario is the same on a couple of other databases that I tried running the code on. Some of the forms will import, then the code fails. I'm 99% sure that these are native A2003 databases and haven't been converted from an earlier version. As to why the original code is failing and not importing all the objects, I don't know. It may well remain a mystery of the ages. At least now I know that in a worst case scenario if I ever have to use this, I can export all the objects to text using the original code and then use the LoadFromText method to import them all one by one. Quite laborious, but it would work. I intend to experiment with this further when I have a bit more time on my hands and will post if I get to the root of the problem. I encountered the same error trying to export/import an MDB as text: Error 2205 (Database Name) can't create the output file. I was using the same posted code. The error occurred at the same place as Wayne's attempt: app.LoadFromText acForm, Name, FileName Same error was thrown for reports as for forms, however qry defs and modules re-loaded without error. Wayne wondered if the error related to compatibility between versions (a converted Access 2000 database). Mine was a converted Access 2000 database as well, so I did some experimenting. Starting from scratch, I converted the Access 2000 mdb to Access 2003 using the 'Convert Database' menu tool. The resulting database consistently threw error 2205 with this code. I then created a new (empty) 2003 mdb and imported all objects from the original mdb. The resulting database was error-free when running the text export/import code. This confirms that the problem likely relates to non-native 2003 databases (converted from Access 2000). Apparently when Access imports objects from A2000 into A2003 it creates 'pure' A2003 objects, whereas the 'Convert Database' menu tool yields some obscure differences. FWIW, I have included below (after the quoted text from Wayne) the 2 text files from the same (simple) form, (1) exported from the A2000-converted-to-A2003, and (2) exported from the native A2003 version. There are 2 lines in the native 2003 version which are not in the converted version: --------------- GUID = Begin 0x8b8505f89b28904d82001a842a850a56 End NameMap = Begin 0x0acc0e5500000000000000000000000000000000000000000c00000002000000, 0x0000000000000000000000000000 End --------------- I am unsure if these lines might be related to the error, but in general my results suggest that importing all objects into a new 2003 database is a better strategy for conversion than using Access' menu tool, at least for text export/import functionality, but perhaps for other (hidden) functionality as well. One other note. My mdb had a reference to a local MDE code library. The referenced MDE was created in Access 2000, so I originally suspected this lib ref. was the culprit. However, using the imported (native 2003) database, there was no error with or without the Access 2000 MDE lib ref. Using the converted version, the error was consistently thrown, with or without the reference. Chris -- Chris Martin UNC-CH >> A little further search brings up this error described but no >> suggestion as to its cause or cure. If you were to post the text file [quoted text clipped - 29 lines] > I intend to experiment with this further when I have a bit more time > on my hands and will post if I get to the root of the problem. ----------------------- form from Access 2000 converted to Access 2003 ----------------------- Version =20 VersionRequired =20 Checksum =1319579709 Begin Form AutoResize = NotDefault RecordSelectors = NotDefault NavigationButtons = NotDefault DividingLines = NotDefault DefaultView =0 ScrollBars =0 TabularFamily =0 BorderStyle =3 PictureAlignment =2 DatasheetGridlinesBehavior =3 GridX =24 GridY =24 DatasheetFontHeight =10 Left =270 Top =210 Right =8910 Bottom =4530 DatasheetGridlinesColor =12632256 RecSrcDt = Begin 0x805fd4ecd754e340 End Caption ="Form not ready" DatasheetFontName ="Arial" Begin Begin Label BackStyle =0 FontName ="Tahoma" End Begin CommandButton FontSize =8 FontWeight =400 ForeColor =-2147483630 FontName ="Tahoma" End Begin Section Height =2880 BackColor =-2147483633 Name ="Detail" GUID = Begin 0xa45a656675e46f489f624edcd2dbe040 End Begin Begin Label OverlapFlags =85 TextAlign =2 Width =5580 Height =600 FontSize =16 Name ="Label0" Caption ="Form not ready." GUID = Begin 0xc096deb6ece8ae41af262a11be30cb7e End End Begin CommandButton OverlapFlags =85 Left =2100 Top =1140 Width =1035 Height =405 Name ="cmdClose" Caption ="Close" OnClick ="[Event Procedure]" GUID = Begin 0x663bfb42d6a6f246a33b57112e840d45 End End End End End End CodeBehindForm Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = True Attribute VB_PredeclaredId = True Attribute VB_Exposed = False Option Compare Database Option Explicit Private Sub cmdClose_Click() On Error GoTo Err_cmdClose_Click DoCmd.Close Exit_cmdClose_Click: Exit Sub Err_cmdClose_Click: MsgBox err.Description Resume Exit_cmdClose_Click End Sub ----------------------- ----------------------- ----------------------- ----------------------- form from Access 2003 imported from Access 2000 ----------------------- Version =20 VersionRequired =20 Checksum =-416531212 Begin Form AutoResize = NotDefault RecordSelectors = NotDefault NavigationButtons = NotDefault DividingLines = NotDefault DefaultView =0 ScrollBars =0 TabularFamily =0 BorderStyle =3 PictureAlignment =2 DatasheetGridlinesBehavior =3 GridX =24 GridY =24 DatasheetFontHeight =10 Left =270 Top =210 Right =8910 Bottom =4530 DatasheetGridlinesColor =12632256 RecSrcDt = Begin 0x805fd4ecd754e340 End GUID = Begin 0x8b8505f89b28904d82001a842a850a56 End NameMap = Begin 0x0acc0e5500000000000000000000000000000000000000000c00000002000000 , 0x0000000000000000000000000000 End Caption ="Form not ready" DatasheetFontName ="Arial" Begin Begin Label BackStyle =0 FontName ="Tahoma" End Begin CommandButton FontSize =8 FontWeight =400 ForeColor =-2147483630 FontName ="Tahoma" End Begin Section Height =2880 BackColor =-2147483633 Name ="Detail" GUID = Begin 0xb7565ebe5f29f74c92a186f19a1e8405 End Begin Begin Label OverlapFlags =85 TextAlign =2 Width =5580 Height =600 FontSize =16 Name ="Label0" Caption ="Form not ready." GUID = Begin 0x5e6a4c00564dfc4d90ae25a2e161fb30 End End Begin CommandButton OverlapFlags =85 Left =2100 Top =1140 Width =1035 Height =405 Name ="cmdClose" Caption ="Close" OnClick ="[Event Procedure]" GUID = Begin 0x0b9721ce18424949a071843075903f42 End End End End End End CodeBehindForm Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = True Attribute VB_PredeclaredId = True Attribute VB_Exposed = False Option Compare Database Option Explicit Private Sub cmdClose_Click() On Error GoTo Err_cmdClose_Click DoCmd.Close Exit_cmdClose_Click: Exit Sub Err_cmdClose_Click: MsgBox Err.Description Resume Exit_cmdClose_Click End Sub On May 23, 11:26 pm, Chris Martin <nr.cmartin.dfgh....@med.unc.edu> wrote: > I encountered the same error trying to export/import an MDB as text: > [quoted text clipped - 26 lines] > There are 2 lines in the native 2003 version which are not in the > converted version: Thanks for the reply (and detective work) Chris. I'll import all objects into a new A2003 database and see how it goes. > I've read that one method of repairing a misbehaving database is to > save all database objects as text and then rebuild them from the text > files. I've used the following code posted by Lyle Fairfield to > accomplish the first step: [snip] > How do I then rebuild the database objects from the text files that > have been created? Just as there is a SaveAsText() function there is also a LoadFromText() function. The latter is whaty you would use to rebuild the objects.  SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.