[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Access VBA Create New SQL Database

Posted on 2012-08-28
Medium Priority
Last Modified: 2012-08-30
Hi. I was given the following SQL statement to create a new SQL database
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
    End With
    Set cmd = Nothing
    Set con = Nothing
    Exit Sub
    MsgBox Err.Description
End Sub
Question by:Murray Brown
LVL 13

Accepted Solution

Ryan earned 2000 total points
ID: 38340762
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.

Author Closing Comment

by:Murray Brown
ID: 38351980
Thanks for the advice. Much appreciated

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question