Issue w/ AutoNumber Field during DAO Record Insert

All -

I have reviewed the sample code at
http://www.bygsoftware.com/Excel/SQL/UsingSql.html
regarding the creation of an Access Table from Excel utilizing the DAO
library.

Below is the sample Create Table SQL Stmt from their web site:

vtSql = ""
vtSql = vtSql & " CREATE TABLE " & ctSheet & " ("
''Loop around each column to create the SQL code
''Column names must not contain spaces
With ActiveCell.CurrentRegion
For viCount = 1 To viCols
vtSql = vtSql & .Cells(1, viCount) & "x " & _
fGetCellFormat(.Cells(2, viCount))
If viCount <> viCols Then
vtSql = vtSql & ", "
Else
vtSql = vtSql & ")"
End If
Next
End With

dbs.Execute vtSql

(ctSheet in the above example is Access Table Name & a Worksheet Tab Name in
Excel. The fGetCellFormat function is in the module. The code picks up the
Field Names from the Worksheet).


I wanted to add an AutoNumber field to identify each record as unique. So I
research the web and found this piece of code. It worked and when added to
the above code it created the AutoNumber field I desired.


'Insert AutoNumber Field
Set t= dbs.TableDefs(ctSheet)
Set f= t.CreateField("xAutoNumberField", dbLong)
f.Attributes = f.Attributes + dbAutoIncrField
f.OrdinalPosition = 0
t.Fields.Append f


(Note: all the proper Dim Stmts are in the procedure)


---- So far so good. The Table has all the fields & the AutoNumber Field.


The next procedure I run is the Insert SQL (also from the
www.bygsoftware.com web site).


vtSql = ""
vtSql = vtSql & " INSERT INTO " & ctSheet
vtSql = vtSql & " VALUES ("
For viCount = 1 To viCols
Select Case fGetCellFormat(.Cells(2, viCount))
Case "TEXT"
vtWrapChar = """"
Case "DATETIME"
vtWrapChar = "#"
Case Else
vtWrapChar = ""
End Select

vtSql = vtSql & vtWrapChar & _
..Cells(viRcount, viCount) & vtWrapChar

If viCount <> viCols Then
vtSql = vtSql & ","
Else
vtSql = vtSql & ")"
End If
Next

dbs.Execute vtSql


(The above SQL is picking up the same fields headers as created in the Table
SQL above with the exception of the AutoNumber Field).


Here is the issue:

None of the Insert Records loaded.


Questions:
1. Am I supposed to modifed the INSERT INTO SQL to account for the new
AutoNumber Field? If yes, how do I do that?

2. The f.OrdinalPosition = 0 stmt in the CREATE TABLE procedure does not
place the AutoNumber field in the 1st position in the Access Table. It
placed the AutoNumber field in 2nd position (it looks like this:
CompanyName, xAutoNumberField, SalesPersonID, etc. instead of this:
xAutoNumberField, CompanyName, SalesPersonID, etc.)


Thank you for your help.


MSweetG222

All -

*****-Blog has been working on the same topic. Belwo is the link.

The difference in "*****-Blog's" "Insert Into" Stmt than "bygsoftware's" is
that in *****-Blog SQL Stmt lists the Column Header to be loaded w/in the SQL
stmt. When I added this to my SQL code, I did not have any further issues.

Here is the link to his site:

http://www.*****-blog.com/archives/2...tables-in-vba/


Thx
MSweetG222

=========================================

"MSweetG222" wrote:

> All -
>
> I have reviewed the sample code at
> http://www.bygsoftware.com/Excel/SQL/UsingSql.html
> regarding the creation of an Access Table from Excel utilizing the DAO
> library.
>
> Below is the sample Create Table SQL Stmt from their web site:
>
> vtSql = ""
> vtSql = vtSql & " CREATE TABLE " & ctSheet & " ("
> ''Loop around each column to create the SQL code
> ''Column names must not contain spaces
> With ActiveCell.CurrentRegion
> For viCount = 1 To viCols
> vtSql = vtSql & .Cells(1, viCount) & "x " & _
> fGetCellFormat(.Cells(2, viCount))
> If viCount <> viCols Then
> vtSql = vtSql & ", "
> Else
> vtSql = vtSql & ")"
> End If
> Next
> End With
>
> dbs.Execute vtSql
>
> (ctSheet in the above example is Access Table Name & a Worksheet Tab Name in
> Excel. The fGetCellFormat function is in the module. The code picks up the
> Field Names from the Worksheet).
>
>
> I wanted to add an AutoNumber field to identify each record as unique. So I
> research the web and found this piece of code. It worked and when added to
> the above code it created the AutoNumber field I desired.
>
>
> 'Insert AutoNumber Field
> Set t= dbs.TableDefs(ctSheet)
> Set f= t.CreateField("xAutoNumberField", dbLong)
> f.Attributes = f.Attributes + dbAutoIncrField
> f.OrdinalPosition = 0
> t.Fields.Append f
>
>
> (Note: all the proper Dim Stmts are in the procedure)
>
>
> ---- So far so good. The Table has all the fields & the AutoNumber Field.
>
>
> The next procedure I run is the Insert SQL (also from the
> www.bygsoftware.com web site).
>
>
> vtSql = ""
> vtSql = vtSql & " INSERT INTO " & ctSheet
> vtSql = vtSql & " VALUES ("
> For viCount = 1 To viCols
> Select Case fGetCellFormat(.Cells(2, viCount))
> Case "TEXT"
> vtWrapChar = """"
> Case "DATETIME"
> vtWrapChar = "#"
> Case Else
> vtWrapChar = ""
> End Select
>
> vtSql = vtSql & vtWrapChar & _
> .Cells(viRcount, viCount) & vtWrapChar
>
> If viCount <> viCols Then
> vtSql = vtSql & ","
> Else
> vtSql = vtSql & ")"
> End If
> Next
>
> dbs.Execute vtSql
>
>
> (The above SQL is picking up the same fields headers as created in the Table
> SQL above with the exception of the AutoNumber Field).
>
>
> Here is the issue:
>
> None of the Insert Records loaded.
>
>
> Questions:
> 1. Am I supposed to modifed the INSERT INTO SQL to account for the new
> AutoNumber Field? If yes, how do I do that?
>
> 2. The f.OrdinalPosition = 0 stmt in the CREATE TABLE procedure does not
> place the AutoNumber field in the 1st position in the Access Table. It
> placed the AutoNumber field in 2nd position (it looks like this:
> CompanyName, xAutoNumberField, SalesPersonID, etc. instead of this:
> xAutoNumberField, CompanyName, SalesPersonID, etc.)
>
>
> Thank you for your help.
>
>
> MSweetG222
>