Click here to Skip to main content
15,886,919 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
After days of searching and trying different solutions, I give up and hope that someone can help me.
I have an Access 2007 database named DB2023.accdb with several tables.
I created with ADOX a new empty database named DB2024.accdb. Both are located in C:\Temp.
With codebehind (using VB), I want to copy all tables and their columns (but without their data) from DB2023 into DB2024.
Of course, I can hard-code the creation, but hope there is a simpler way.
Looking forward to suggestions.

What I have tried:

Have not found any example for this explicit task. The closest I got, is
VBScript
Dim Filename = "C:\Temp\DB2023.accdb"
     Dim ConnString As String = _
     "Provider=Microsoft.ACE.OLEDB.12.0; Data source =" & Filename
     Dim cn As New OleDbConnection(ConnString)
     Dim dbCmdInsert As New OleDbCommand

     cn.Open()
     Dim restrictions() As String = New String(3) {}
     restrictions(3) = "Table"
     Dim AllTables As DataTable = Nothing
     AllTables = cn.GetSchema("Tables", restrictions)
     GridView1.DataSource = AllTables
     GridView1.DataBind()


Resulted in a table with Table_Name and Table_Type and creation/modifying data.
But how to get column data for each table?
I assume that
VBScript
For EACH row as DataRow in Alltables.Rows
' what to do here to fetch all columns name and properties
Next

is the way, but details??
Posted
Updated 8-Sep-23 10:17am
v2

The simplest way is to copy the entire database as a file, then connect to it and delete all the information. That way, you also copy any stored procedures, functions, triggers, and so on as well as the table format.
 
Share this answer
 
Comments
Member 14168791 9-Sep-23 3:00am    
Thanks for the input - why do it in a more complicated way? The advance is that your solution also takes care of the last made changes i the structure of the database.
Thanks!
OriginalGriff 9-Sep-23 4:20am    
You're welcome!
Just keep a blank copy of the database file with your app, either in the file system or in your app resources, and just make a copy every time you need a new database.
 
Share this answer
 
Comments
OriginalGriff 8-Sep-23 8:16am    
The only reason I don't suggest that is that it's too easy to forget to update that when you add or change an SP for example.
Dave Kreskowiak 8-Sep-23 8:20am    
Yeah. I just don't use Access databases AT ALL. Making LOB applications dependent on Office is a really bad idea with all the updates and changes they make.

Plus, the limitations on the engine make large solutions impossible. Even the 64-bit version of Access still has 2GB file limit. Ask me how I know.
OriginalGriff 8-Sep-23 9:41am    
Likewise - I use SqLite for single user DB's and SQL Server for multiuser.
Member 14168791 9-Sep-23 3:16am    
Red your comments and agree. But using SQL server in the web-hotel increases the cost for our poor ngo with almost 100 times - not to think about. The size of our DB is only 2 MB so the limits of the Provider (=Microsoft.ACE.OLEDB.12.0;) is far away.
But thanks for your inputs - appreciated!
OriginalGriff 9-Sep-23 4:25am    
If you are using this in a multiuser situation, then Access (or any other non-server based DB Engine) is going to give problems: lots of them that are a real PITA to work round.
There is a free edition of SQL Server which for a small app would probably never hits it's limitations:
https://www.microsoft.com/en-us/sql-server/sql-server-downloads#:~:text=Express,web%2C%20and%20small%20server%20applications.
You would want two copies: one for dev and one for prod on separate machines - never try to develop against the production DB! :D

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900