Create new datadase from Visual Basic

[nguyentaiuyenchi]

Hi all,
I make an application with VB and SQL server7.0.
I want create new database by VB coding and tranfer some data from current database to new database that has just been created.
I don't know how to do.
Thank you for helping.
Uyen Chi.

 

[jjrockman]

Hi.

If you are familiar with SQL Server's Transact-SQL Language, there are special commands that are outside of the ANSI SQL specifications. To create a database, run a SQL Statement against your SQL Server - "CREATE DATABASE myDB" without the quotes, and replacing myDB with whatever you want your new database name to be. To create tables within your new database, use something like "CREATE TABLE myDB.newTable (col1 INT)" where myDB is your new database name, newTable is the new table name, and col1 is the first column name (of type INT). You will most likely have many more columns than I have here.
I am assuming that a) the new database is on the same instance of SQL Server, and b) that the user you are logged in as on the SQL Server has appropriate permission to create databases and tables.

Hopefully this helps! It has been a few years since I've really worked with VB/SQL Server, so you may want to check the SQL Books Online...

 

[nguyentaiuyenchi]

Thank you your response.
Maybe my question is not clearly.
I have already created a database (ex: its name is Account2001), there are many tables in 'Account2001'.
User can insert, update... via my application (it is made by Visual Basic). However, I have to create new database every year (ex: next year I need 'Account2002', and next 'account2003'...).

Creating database must do via my application. I think that I have to create a store and execute it by ADODB....
but I can not do it.

Thanks.

 

[jjrockman]

If your application already runs SQL statements like INSERT, UPDATE, SELECT - then replace INSERT INTO myTable...etc with CREATE DATABASE myDB. The CREATE DATABASE and CREATE TABLE statements are just SQL statements that work on database objects instead of data.

 

[CodeFish]

Hi nguyentaiuyenchi,

To create Databases using VB Code use ADOX (Microsoft ADO Ext 2.6 for DDL and Security msadox.dll).

a simple example:-

Public Function CreateDatabase(sConnectionString As String) As ADOX.Catalog

On Error GoTo ADOXErrHandler

'Declare Variables
Dim oAdoCat As ADOX.Catalog 'ADOX Catalog Object

'Create ADOX Catalog Object
Set oAdoCat = New ADOX.Catalog

'Create Database Using Connection String
oAdoCat.Create sConnectionString

'Returns True if Function was Completed Successfully
Set CreateDatabase = oAdoCat

'Release ADOX Object Reference
Set oAdoCat = Nothing

Exit Function

ADOXErrHandler:
'Error
'DataBase Wasn't Created Due to Error or it Already Exists
Set oAdoCat = Nothing

End Function


Hope this helps,

Codefish