Creating an access table with ADO

Does anyone know how to turn this into a Loop? I don't want to hardcode my
table field names. Thank you in advance!

Field1 = ActiveWorkbook.Worksheets("mysheet1").Range("C1").Value
Field2 = ActiveWorkbook.Worksheets("mysheet2").Range("D1").Value
Field3 = ActiveWorkbook.Worksheets("mysheet3").Range("E1").Value

cnn.Execute _
"CREATE TABLE tblOutput(" & _
Field1 & " VARCHAR(1) NOT NULL," & _
Field2 & " VARCHAR(20) NOT NULL," & _
Field3 & " VARCHAR(20) NOT NULL)"

What are you trying to do? Are you trying to populate at table that you
create on the fly or are you trying to create multiple tables on the fly? It
is hard to tell from what you have here.

"JJ" wrote:

> Does anyone know how to turn this into a Loop? I don't want to hardcode my
> table field names. Thank you in advance!
>
> Field1 = ActiveWorkbook.Worksheets("mysheet1").Range("C1").Value
> Field2 = ActiveWorkbook.Worksheets("mysheet2").Range("D1").Value
> Field3 = ActiveWorkbook.Worksheets("mysheet3").Range("E1").Value
>
> cnn.Execute _
> "CREATE TABLE tblOutput(" & _
> Field1 & " VARCHAR(1) NOT NULL," & _
> Field2 & " VARCHAR(20) NOT NULL," & _
> Field3 & " VARCHAR(20) NOT NULL)"

I'm trying to create a table on the fly based on the field names that are
typed into the excel spreadsheet. It works the way I indicated in my first
message, however I want to turn it into a loop so it will create a table
based on how many fields I decide to type into the spreadsheet. The field
names start at cell C1 in "mySheet1" (sorry in my previous msg it shows 3
different worksheets but it should only be mySheet1). I hope this makes
sense. Thanks.

"Jim Thomlinson" wrote:

> What are you trying to do? Are you trying to populate at table that you
> create on the fly or are you trying to create multiple tables on the fly? It
> is hard to tell from what you have here.
>
> "JJ" wrote:
>
> > Does anyone know how to turn this into a Loop? I don't want to hardcode my
> > table field names. Thank you in advance!
> >
> > Field1 = ActiveWorkbook.Worksheets("mysheet1").Range("C1").Value
> > Field2 = ActiveWorkbook.Worksheets("mysheet2").Range("D1").Value
> > Field3 = ActiveWorkbook.Worksheets("mysheet3").Range("E1").Value
> >
> > cnn.Execute _
> > "CREATE TABLE tblOutput(" & _
> > Field1 & " VARCHAR(1) NOT NULL," & _
> > Field2 & " VARCHAR(20) NOT NULL," & _
> > Field3 & " VARCHAR(20) NOT NULL)"

Here's one example:

http://www.utteraccess.com/forums/sh...?Number=418108

I.e.,

cn2.Execute "Create Table " & tmpExTbl & "(" & _
Join(myArr, " varchar (50), ") & " varchar (50))"

It gives you a little less control on your field types...

The key is to generate the correct, dynamic string. More on Create Table:
http://msdn.microsoft.com/library/en.../D2/S5A320.asp

Regards,
Nate Oliver

Nate Oliver wrote:
> More on Create Table:
>
http://msdn.microsoft.com/library/en.../D2/S5A320.asp

That's link is for the Jet 3 help. For Jet 4 (the most recent version
of Jet) the CREATE TABLE syntax has changed significantly. Here is the
equivalent link to the Jet 4.0 SQL help:

http://office.microsoft.com/en-us/as...322201033.aspx

Jamie.

--