I must create an Access database from the script. Ideally, I'd love something such as the way in which SQL scripts may be used to produce a SQL database.
Is possible whatsoever? Can there be something that would leverage the SQL scripts I curently have? I wish to produce the same database structure as my SQL database.
Tell me if you want me to explain anything. Thanks.
You should use DDL to produce tables, but so far as I understand Access' (Jet/ACE) database engine doesn't support a CREATE DATABASE statement. You should use DAO to produce the brand new database.
Public Function CreateEmptyDB(ByVal pstrNewDbPath As String) As Boolean Dim db As DAO.Database Dim blnRetValue As Boolean blnRetValue = False 'dbVersion40 => 2002-2003 format ' Set db = DBEngine.CreateDatabase(pstrNewDbPath, dbLangGeneral, dbVersion40) db.Close Set db = Nothing blnRetValue = True CreateEmptyDB = blnRetValue End Function
Once you have produced the database, you are able to execute "CREATE TABLE" claims.
But when you have a current SQL Server database you need to re-create in Access, it may be simpler to import the tables by having an ODBC link with SQL Server. When posting you are able to choose tables using their data, or just the table structures.
Update: Using VBA, DoCmd.TransferDatabase can import a table from an ODBC database. This situation was modified from the sample in Access' TransferDatabase help subject:
DoCmd.TransferDatabase acImport, "ODBC Database", _ "ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _ & "DATABASE=pubs", acTable, "Authors", "dboAuthors", True
I added True because the StructureOnly parameter to import the table structure with no data. Change it out to False if you wish to import the information too.
You'll be able to create an access database by code, either with DDL instructions of by adjusting ADO or DAO objects.
I doubt that DDL T-SQL code could be useful to produce an Access database: plenty of instructions won't be understood in Access, from area types to indexes and constraints.
One option is to make use of ADODB connections for connecting both towards the original SQL database and also the recently produced Access database (see #HansUp proposal), and employ ADOX Object Model. You may then have the ability to 'read' the SQL database (ie the ADOX Catalog) and it is objects and recreate objects with 'similar' qualities around the Access side: tables, fields, indexes, relations, etc.
Such like might be completed with DAO Object Model, however i guess it will likely be simpler with ADOX.
Another option is to see if existing softwares can have the desired effect. EMS SQL Manager is definitely an option.
I wound up choosing my very own solution. I possibly could not get either from the first couple of to operate perfectly. I produced two executables: someone to produce the database and something to operate scripts.
For that application that produces the database, I added the COM reference "Microsoft ADO Ext. 2.8 for DDL and Security". The code is really fairly simple: (Replace "test.mdb" using the proper file path for the file.)
Dim cat As ADOX.Catalog = New ADOX.Catalog() cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb;Jet OLEDB:Engine Type=5")
To operate the scripts, I produced an easy parser to see and run "Access scripts". These scripts derive from SQL scripts, for the reason that they offer a listing of instructions to operate. For instance, a script may be understood to be:
--Create the table. CREATE TABLE [Test] ([ID] Number, [Foo] Text(255)) --Add data to the table. INSERT INTO [Test] ([ID], [Foo]) VALUES (1, 'Bar')
This is actually the code for that parser. (Replace "test.mdb" using the proper file path for the file.)
Dim textStream = File.OpenText(scriptPath) Dim lines As List(Of String) = New List(Of String) While textStream.Peek() <> -1 lines.Add(textStream.ReadLine()) End While textStream.Close() Dim connection As OleDb.OleDbConnection = New OleDb.OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb") connection.Open() For Each line As String In lines If Not String.IsNullOrEmpty(line) Then If Not line.StartsWith("--") Then Dim dbCommand = New OleDb.OleDbCommand(line, connection) dbCommand.ExecuteNonQuery() End If End If Next connection.Close()
This solution is effective and was really quite simple to implement.