• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 661
  • Last Modified:

Access VBA Create New SQL Database

Hi. I was given the following SQL statement to create a new SQL database
CREATE DATABASE MyDatabaseName;
Below is my code to create a new table in SQL using Access VBA. How do I alter the connection string?


Sub oCreate_Online_Table
Dim  sSQL As String

sSQL = "CREATE TABLE Categories "
sSQL = sSQL & "([ID] char not null primary key, [Main Category] nvarchar(30) not null, [Sub Category] nvarchar(30) not null)"
   
    On Error GoTo EH
   
    Set con = New ADODB.Connection
    con.Open "Provider=SQLOLEDB;Data Source=41...100;Network Library=DBMSSOCN;Initial Catalog=MagicBox_Doppio;User ID=x...g;Password=bvcv;"
             
    Set cmd = New ADODB.Command    
    With cmd
        .CommandText = sSQL
        .CommandType = adCmdText
        .ActiveConnection = con
        .Execute
    End With
   
   
    Set cmd = Nothing
    Set con = Nothing
    Exit Sub
EH:  
    MsgBox Err.Description
End Sub
0
Murray Brown
Asked:
Murray Brown
1 Solution
 
RyanProject Engineer, ElectricalCommented:
A new database in Access is essentially a new Access file.  In general, I wouldn't ever write code to make a new database, just do that manually.  Creating tables dynamically, is ok, but that too can blowup a database in a hurry if it goes awry.

Have a look at the following and see if that clears up the connection string question.
http://www.connectionstrings.com/access
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks for the advice. Much appreciated
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now