Create an access database by a VBA in Excel

B

Blasphemer

New Member
Joined
Sep 21, 2008
Messages
42
Hi,

I am looking to create an Access database from VBA in Excel. But I am unable to "create the database" and or open one if its already created. Here's the code I have so far. Any help?

Code: 
Option Explicit

Public Sub sub_SendToAcces()

    Dim accApp As Access.Application
    Dim accDB As Object
    
    Set accApp = New Access.Application
    ' code to create a new database
    
    ' code to open it and set it in accDB variable

End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Greetings,

Not sure what versiion you are using, but other than dimming one or two variables and changing the string path, this is straight from the vba help file in Access (2003). Try 'NewCurrentDatabase'

Code: 
Sub NewAccessDatabase()
    Dim appAccess As Object
    Dim dbs As Object, tdf As Object, fld As Variant
    Dim strDB As String
    Const DB_Text As Long = 10
    Const FldLen As Integer = 40
    ' Initialize string to database path.
    strDB = "C:\Documents and Settings\mark\Desktop\NewTemp\Newdb.mdb"
    ' Create new instance of Microsoft Access.
    Set appAccess = _
        CreateObject("Access.Application.11")
    ' Open database in Microsoft Access window.
    appAccess.NewCurrentDatabase strDB
    ' Get Database object variable.
    Set dbs = appAccess.CurrentDb
    ' Create new table.
    Set tdf = dbs.CreateTableDef("Contacts")
    ' Create field in new table.
    Set fld = tdf. _
        CreateField("CompanyName", DB_Text, FldLen)
    ' Append Field and TableDef objects.
    tdf.Fields.Append fld
    dbs.TableDefs.Append tdf
    Set appAccess = Nothing
End Sub

The example creates an saves a db with one table (said table having one field).

Hope this helps,

Mark
 
Upvote 0
Hello Mr Excel, the code above is great but how do I create and add multiple fields into the database, thank you
 
Upvote 0
Repeat the lines commented as adding fields and appending them, as many times as you need.
 
Upvote 0
No, that line creates a table. To create fields (which have to be in a table), you repeat these lines:

Code: 
    ' Create field in new table.
Set fld = tdf. _
CreateField("CompanyName", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

though you may need to alter the length and field type to suit your data.
 
Upvote 0
Th
RoryA said:
No, that line creates a table. To create fields (which have to be in a table), you repeat these lines:

Code: 
    ' Create field in new table.
Set fld = tdf. _
CreateField("CompanyName", DB_Text, FldLen)
' Append Field and TableDef objects.
tdf.Fields.Append fld

though you may need to alter the length and field type to suit your data.
Click to expand...
ank you that part works great, but is there a code that can automatically pull data from the excel sheet to the created fields, for example the code above created a field call Employee name in access database, there is data in the spreadsheet, how can I automatically fill this into the database, within the same module, thanks in advance
 
Upvote 0
That’s a completely different question so doesn’t really belong in this thread. I suggest you post it as a new one.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Looking for Help to create an Excel or Access Database
Replies
5
Views
175
Micron
M
E
  • Question
Run acces macro in vba code
Replies
4
Views
233
Joe4
Joe4
L
  • Solved
Querying access db from excel - 32 bit to 64 bit conversion
Replies
2
Views
145
JEC
JEC
A
  • Question
Recording Excel ranges from several tabs into Access DB
Replies
1
Views
398
alansidman
A
B
  • Solved
Use Sheet1:D5 to find matching cell in Sheet2 Column A:A, and select cell (or row)
Replies
1
Views
111
brianv
B

Forum statistics

Threads
1,216,326
Messages
6,130,057
Members
449,555
Latest member
maXam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top