Creating a table in access from excel

[peter_z]

Hey Guys, trying to get this code to work:

 strSql = "CREATE TABLE ThisTable " _
& "(Client_name CHAR) values('" & LOAD_CLIENT_NAME & "');"

Just trying to create a table with the heading client_name and then load some looping data under that heading.

Can anyone spot where my code is going wrong?

Cheers in advance!

 

[xenou]

You probably have to first create the table, then load it with values ... two separate statements.
ξ

 

[peter_z]

Yup I can't figure out any other plan other than that as well. Cheers for the feedback.

 

[pcc]

I use this code. Adapt to suit.
you need a sheet ('New_table') with Table field names in row 1 and field type in row 2

Sub Create_Table()
Dim adoxCatalog As ADOX.Catalog
Dim adoxTable As ADOX.Table
Dim cnn As ADODB.Connection
On Error GoTo quit
strdb = Range("file_location").Value  ' location of database
'Establish connection to the database
Set cnn = New Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strdb & ";" & _
"Jet OLEDB:Database Password=" & pw & ";"
    
'Now associate our connection to a Catalog object
Set adoxCatalog = New ADOX.Catalog
Set adoxCatalog.ActiveConnection = cnn
Sheets("New_table").Activate
lc = [a1].CurrentRegion.Columns.Count
' get rid of any leading or trailing spaces
For c = 1 To lc
Cells(1, c) = Trim(Cells(1, c))
Next c
' now get rid of illegal characters
On Error Resume Next
Rows(1).Select
 Selection.Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False
'    Selection.Replace What:="/", Replacement:="", LookAt:=xlPart, _
'        SearchOrder:=xlByColumns, MatchCase:=False
'    Selection.Replace What:="\", Replacement:="", LookAt:=xlPart, _
'        SearchOrder:=xlByColumns, MatchCase:=False
'    Selection.Replace What:="?", Replacement:="", LookAt:=xlPart, _
'        SearchOrder:=xlByColumns, MatchCase:=False
If Err.Number <> 0 Then Err.Clear
On Error GoTo quit
[a1].Select
'Create the table definition
Set adoxTable = New ADOX.Table
With adoxTable
    .Name = Range("Table").Value  ' name of table to be created
    For c = 1 To lc
    colvar = Cells(1, c)
    coltype = Cells(2, c)
    Select Case coltype
    Case "Integer"
    .Columns.Append colvar, adInteger 'long integer field
    Case "Single"
    .Columns.Append colvar, adSingle 'single  integer field
    Case "Double"
    .Columns.Append colvar, adDouble 'single  integer field
    Case "Text"
    .Columns.Append colvar, adVarWChar, 100  ' text field
    Case "Currency"
     .Columns.Append colvar, adCurrency ' currency field
      Case "Date"
     .Columns.Append colvar, adDate ' date field
   ' Case "Memo"
  '  .Columns.Append colvar, admemo ' memo field
    Case Else ' default to text
      .Columns.Append colvar, adVarWChar, 100  ' text field
    End Select
    
    Next c

   
               '  .Keys.Append "PrimaryKeyItemID", adKeyPrimary, "Pers"
End With
'Append the table to the database
adoxCatalog.Tables.Append adoxTable
'Clean up and close
cnn.Close
resp = MsgBox("Table " & Range("Table").Value & " has been created", vbInformation)
Exit Sub
quit:
resp = MsgBox("An error has occurred. Maybe the table already exists", vbExclamation)
End
End Sub