Macro for creating an Access database

[Piulitsa]

Hey everyone.

I have the code for creating a database in Access. But when the code gets to "Creating the table", I get an error (picture attached).

Here is my code (it worked 10 months ago..). It should create the file, and then execute a query that creates a table with some fields.

Sub CreateDatabase()

'Scop Macro: Creaza o baza de date de acces
    Dim conexiune_database As String
    Dim informatii_catalog As Object
    Dim conexiune_cu_date As ADODB.Connection
    Dim locatie_database As String



    'numele bazei de date
    locatie_database = "E:" & Application.UserName & ".mdb" '(adica numele utilizatorului)
    conexiune_database = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & locatie_database & ";"

    'Creeare baza de date noua
    Set informatii_catalog = CreateObject("ADOX.Catalog")
    informatii_catalog.Create conexiune_database
    Set informatii_catalog = Nothing

    'conectare si introducere date
    Set conexiune_cu_date = New ADODB.Recordset

    'conexiune_cu_date.Open    
    With conexiune_cu_date
    
        .Execute "CREATE TABLE powerbi ([build_number] text(150) WITH Compression, " & _
                 "[race_type] text(150) WITH Compression, " & _
                 "[race_number] text(50) WITH Compression, " & _
                 "[platform] text(100) WITH Compression, " & _
                 "[tier] text(50) , " & _
                 "[network] text(100) , " & _
                 "[speed] text(100) , " & _
                 "[latency] text(100) WITH Compression, " & _
                 "[device] text(100) WITH Compression, " & _
                 "[firmware] text(50)) " & _
                 "[rating] text(100) WITH Compression, " & _
                 "[notes_observations] text(100) WITH Compression), "
                
    End With
    Set conexiune_cu_date = Nothing
        
End Sub

I get this error: "Run-time error '3704': Operation is not allowed when the object is closed."

If I add "conexiune_cu_date.Open", I get another error: Run-time error '-2147467259 (80004005)': [Microsoft][ODBC Manager] Data source name not found and no default driver specified."

Thank you in advance

 

[ranman256]

I've never seen the 'with compression' before.
why are you creating a db? Why not already have the db created, then write data to it?

 

[Piulitsa]

I've never seen the 'with compression' before.
why are you creating a db? Why not already have the db created, then write data to it?

I wanted to automate the whole project from extracting data from multiple workbooks, sort it, create a db and then populating it with that data, by only pressing 1 button.

 

[RoryA]

I don't really understand how that could ever have worked. You've declared a Connection object, then tried to assign a Recordset object to it.

To open a connection, you need to pass the connection details:

    conexiune_cu_date.Open conexiune_database
    With conexiune_cu_date

 

[Piulitsa]

I don't really understand how that could ever have worked. You've declared a Connection object, then tried to assign a Recordset object to it.

bro, you're a genius. now I have another error, in "Create table statement"

.Execute "CREATE TABLE powerbi ([build_number] text(150) WITH Compression, " & _
                 "[race_type] text(150) WITH Compression, " & _
                 "[race_number] text(50) WITH Compression, " & _
                 "[platform] text(100) WITH Compression, " & _
                 "[tier] text(50) , " & _
                 "[network] text(100) , " & _
                 "[speed] text(100) , " & _
                 "[latency] text(100) WITH Compression, " & _
                 "[device] text(100) WITH Compression, " & _
                 "[firmware] text(50)) " & _
                 "[rating] text(100) WITH Compression, " & _
                 "[notes_observations] text(100) WITH Compression), "

 

[RoryA]

Care to give us a clue what the error message is?

Edit: never mind, it must be a syntax error. You have a missing comma and misplaced bracket - it should be:

        .Execute "CREATE TABLE powerbi ([build_number] text(150) WITH Compression, " & _
                 "[race_type] text(150) WITH Compression, " & _
                 "[race_number] text(50) WITH Compression, " & _
                 "[platform] text(100) WITH Compression, " & _
                 "[tier] text(50), " & _
                 "[network] text(100), " & _
                 "[speed] text(100), " & _
                 "[latency] text(100) WITH Compression, " & _
                 "[device] text(100) WITH Compression, " & _
                 "[firmware] text(50), " & _
                 "[rating] text(100) WITH Compression, " & _
                 "[notes_observations] text(100) WITH Compression)"

 

[Piulitsa]

srry. this is the error I'm receiving

 

[RoryA]

See my edited post above.

 

[Piulitsa]

See my edited post above.

Life savior. Thank you

 

[RoryA]

Glad to help.