Convert Web Access database to Normal Access database

Convert Web Access database to Normal Access database - Microsoft Community

Despite this thread isn't the newest one, I had the same issue and found a very efficent way to transfer all objects using a bit VBA and a table.

The whole processing works around the fact that you can save every object of a MS Access databases as text file by using Application.SaveAsText. This applies to objects of a web database as well.

Here is what I came up with:

Create a table "tTransferObjects" with 3 fields in an empty desktop database. All fields with data type Short Text. Field names are:

ObjectType

ObjectName

ObjectFile

Export this table to the web database where you want to get the objects from.

2. Save and close the desktop database and open the web database. You should see the newley created table "tTransferObjects" now.

3. Add a code module to the web database, paste the following two procedures into it and run it:

Option Compare DatabaseOption Explicit' 1. Export all MS Access objects as text files' 2. Fill table with information about objects (Type, Name and Path file). This is used to import the objects again.Public Sub ExportDatabaseObjects() On Error GoTo Err_ExportDatabaseObjects
Dim db As Database Dim td As TableDef Dim d As Document Dim c As Container Dim i As Integer Dim sExportLocation As String
Set db = CurrentDb()
sExportLocation = "C:\YourFolderName\" ' Do not forget the closing back slash!
Set c = db.Containers("Forms") For Each d In c.Documents Application.SaveAsText acForm, d.Name, sExportLocation & "Form_" & d.Name & ".txt" Call InsertValue("Form", d.Name, sExportLocation & "Form_" & d.Name & ".txt") Next d
Set c = db.Containers("Reports") For Each d In c.Documents Application.SaveAsText acReport, d.Name, sExportLocation & "Report_" & d.Name & ".txt" Call InsertValue("Report", d.Name, sExportLocation & "Report_" & d.Name & ".txt") Next d
Set c = db.Containers("Scripts") For Each d In c.Documents Application.SaveAsText acMacro, d.Name, sExportLocation & "Macro_" & d.Name & ".txt" Call InsertValue("Macro", d.Name, sExportLocation & "Macro_" & d.Name & ".txt") Next d
Set c = db.Containers("Modules") For Each d In c.Documents Application.SaveAsText acModule, d.Name, sExportLocation & "Module_" & d.Name & ".txt" Call InsertValue("Module", d.Name, sExportLocation & "Module_" & d.Name & ".txt") Next d
For i = 0 To db.QueryDefs.Count - 1 Application.SaveAsText acQuery, db.QueryDefs(i).Name, sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt" Call InsertValue("Query", db.QueryDefs(i).Name, sExportLocation & "Query_" & db.QueryDefs(i).Name & ".txt") Next i
Set db = Nothing Set c = Nothing
MsgBox "All database objects have been exported as a text file to " & sExportLocation, vbInformation
Exit_ExportDatabaseObjects: Exit Sub
Err_ExportDatabaseObjects: MsgBox Err.Number & " - " & Err.Description Resume Exit_ExportDatabaseObjects
End Sub' Using tObjectTransferPublic Sub InsertValue(sObjectType, sObjectName, sObjectFile) Dim StrSQL As String

    StrSQL = "INSERT INTO tTransferObjects (ObjectType,ObjectName,ObjectFile) VALUES ('" & sObjectType & "','" & sObjectName & "','" & sObjectFile & "');"

DoCmd.SetWarnings False DoCmd.RunSQL StrSQL DoCmd.SetWarnings TrueEnd Sub

When this procedure has finished open the table "tTransferObjects" and check if all the necessary information is present. Save and close the web database now. Check the output folder. You should see a text file for every exported object in your database.

4. Open the desktop database created in step 1 and delete the empty table "tTransferObjects". Import all tables from the web database using the function "Import Access Database" on "External Data", group "Import & Link". Make sure you also import the table "tTransferObjects" from the web database. This table contains all the necessary information we need for the next step (import).

5. In the desktop database add the procedure below and run it:

Public Sub LoadMyObjects()
Dim rs As Recordset Dim sObjectName As String Dim sObjectFile As String
' Use the table now to control the import Set rs = CurrentDb.OpenRecordset("SELECT * FROM tTransferObjects")
'Check to see if the recordset actually contains rows If Not (rs.EOF And rs.BOF) Then rs.MoveFirst Do Until rs.EOF = True
' Make sure the arguments are string sObjectName = Trim(rs!ObjectName) sObjectFile = Trim(rs!ObjectFile)
Select Case rs!ObjectType Case "Form" Application.LoadFromText acForm, sObjectName, sObjectFile Case "Query" Application.LoadFromText acQuery, sObjectName, sObjectFile Case "Report" Application.LoadFromText acReport, sObjectName, sObjectFile Case "Macro" Application.LoadFromText acMacro, sObjectName, sObjectFile Case "Module" Application.LoadFromText acModule, sObjectName, sObjectFile End Select
rs.MoveNext ' Get the next Loop Else MsgBox "There are no records in the recordset." End If
MsgBox "Import finished."
rs.Close 'Close the recordset Set rs = Nothing 'Clean upEnd Sub

6. If everything was working according to plan, you should have all objects in your desktop database now.

No guarantee that the database it will work immediately but you can save a lot of time when using this approach.

Google Sites

Report abuse