Creating Access Tables from excel using ADO

[Zubair57]

Hi,

I want to create Access tables with table names taken from a range of cells in excel.
I am not using the VBA variable in SQL statement correctly. Please see the FOR NEXT construct in the code below
I tried this but doesn't work


Sub ADOCreatenewtables()
On Error Resume Next
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim X As String
strDBName = "ADO2.accdb"
strMyPath = ThisWorkbook.Path
strDB = strMyPath & "\" & strDBName

' open the connection
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open strDB
End With

For Each Cell In Range("K1:K3")
X = Cell.Value
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
'create Table
cmd.CommandText = "CREATE TABLE 'X' (Style Char(10) Primary Key, A int, B int, C Int, RecActive YesNo)"
cmd.Execute , , adCmdText
Next Cell
Set cmd = Nothing
Set cnn = Nothing
Exit Sub
End Sub

 

[Micron]

Have never done this (so take it as you will), but a couple of things come to mind:
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn I think the second set statement may not be correct (cmd.ActiveConnection = cnn)
'create Table

cmd.CommandText = "CREATE TABLE 'X' (Style Char(10) Primary Key, A int, B int, C Int, RecActive YesNo)"

Suggest:
- remove the single quotes around X and use proper concatenation when constructing sql statements with variables (see below *)
- Char(10) does not look like a valid data type to me
- put primary key parameter at the end and specify the field name in parentheses
- use variable names that give you some idea of the data type when you're way down in your code; e.g. not X, but strTblName (str for string)

This should create table X with Style (character) [cannot be NULL, which I think you must define for any field you want to be a primary key], as well as A, B, C (integer), Reactive (Yes/No):

CREATE TABLE X (Style CHAR NOT NULL, A INTEGER, B INTEGER, C INTEGER, RecActive YesNo, PRIMARY KEY (Style))

*Since I never use line continuation characters, I'd Dim a variable for the text construct (Dim strText), which allows me to test the construct before trying to use it (Debug.Print strText) and code it like this, assuming I get the drift:

strText = "CREATE TABLE " & strTblName & " (Style CHAR NOT NULL,  A INTEGER, RecActive YesNo, "
strText = strText & "PRIMARY KEY (Style))"
cmd.CommandText = strText

I'm assuming each table will have the same fields with the same properties - an unusual situation for sure. I'd say it's certainly not a normalized table structure. Did you consider the TransferSpreadsheet function instead of this method?

 

[Joe4]

Cross-posted: Creating Access tables with table names from excel cells

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Zubair57]

Cross-posted: Creating Access tables with table names from excel cells

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Sorry Joe. I will take care next time I post

 

[Zubair57]

Many thanks Micron. Your first suggestion does it for me i.e. "remove the single quotes around X and use proper concatenation when constructing sql statements with variables"
"CREATE TABLE " & X & "(Style Char(10) Primary Key, A int, B int, C Int, RecActive YesNo)"

Actually I have a workbook of SP500 companies, around 500 worksheets. Columns are the same but rows can vary.The sheets are named as ticker symbol for each company. The same ticker symbols are available in an excel range. I am trying to transfer my data from excel to Access

I dont know about the TransferSpreadsheet function. Could you direct me where I can find this? Anyways I would google it as well.
Thanks again