VBA variable Access Column Names

[Lorlai]

Hello Mr. Excel People,

I am trying to create an access database through excel, and am using vba to manually add these parts to the db. I am trying to create a table that has variable fields. The names, types, and number of these fields will be different depending on the spreadsheet that is entered. The standard create table statement:

.Execute "CREATE TABLE tblSample ([Name] text(50), " & _
                 "[Address] text(150), " & _
                 "[City] text(50), " & _
                 "[ProvinceState] text(2), " & _
                 "[Postal] text(6), " & _
                 "[Account] decimal(6))"

just doesn't seem to allow for variable changes. What I would like to have happen is for vba to read the header cells and store these values as the name. (Instead of [Name], [XYZ Co. Emp ID]. Is this possible? Or will I need to make certain everything is standard and hard code this in vba?


Thanks for any help you may provide!

 

[Lorlai]

Doing some more research, I found I could ask this question a little clerarer. I apologize for the double posts; I wasn't certain how to edit the above question.

I am taking a spreadsheet of data. This data contains a header row, followed by rows of data. I have a module that loops through the top row of data, reading the current cell value, and storing that value into a variable (field1). Once the loop reaches a cell without data, the loop ends. I am then trying to take each of these created variables and use the stored information as a field name within an access table. What I have so far is:

Dim field1 As String
Range ("A1").Select
Do
field1=Activecell.Value

And then I get stuck. I want to have a create table statement that will read the field1 value and place that as the field/column name. Is this possible? What steps do I need to take to get through this?

Thank you!