Using codes to create Database Table

[Akinyemi]

I use Ms Access 2000 for my Database Tables.
I tried to use code to create a Database Table for the Tax Software I am developing instead of creating it manually.
Initially I wrote each field on a separate line. But when I wrote above 10 lines VB complained that the statement should not exceed 10 lines so, I then wrote this statement :

Dim conPaye As ADODB.Connection

Set conPaye = New ADODB.Connection
conPaye.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\BIRS6\Tax.mdb'"

conPaye.Execute "CREATE TABLE Paye(" & _
"AssessmentYear Date Number(6),TaxPayerID Smallint(10),Name VarChar(50),Address varchar(50),TownVillage varchar(50),State varchar(50), " & _
"BasicSalary Currency,HousingAllowance Currency,TravellingAllowance Cuurency,UtilityAllowance Currency,MealSubsidyAllowance Currency,EntertainmentAllowance Currency,LeaveGrant Currency,OtherAllowances Currency,TotalAllowances currency,GrossPay Currency, " & _
"PersonalAllowance Currency,ChildrenAllowance Currency,DependantRelativesAllowance Currency,HousingReliefAllowance Currency,TransportReliefAllowance currency,UtilityReliefAllowance Currency,MealSubsidyReliefAllowance Currency,EntertainmentReliefAllowance Currency, " & _
"LifeAssurancePolicy Currency,LeaveGrantReliefAllowance Currency,DisabilityAllowance currency,PensionFund Currency,NationalHousingFund Currency,GrossTaxFreePay Currency,TaxablePay Currency,AnnualTaxPayable Currency,MonthlyTaxPayable Currency);"

Set conPaye = Nothing
End Sub

After writing the above statement I receive this compile error:

'Syntax Error in CREATE TABLE statement'.

I tried several times to detect my mistake but could not.Please kindly point out what is wrong with the above code.
Thank you in advance.
Akinyemi.

[willakawill]

I use Ms Access 2000 for my Database Tables.
I tried to use code to create a Database Table for the Tax Software I am developing instead of creating it manually.
Initially I wrote each field on a separate line. But when I wrote above 10 lines VB complained that the statement should not exceed 10 lines so, I then wrote this statement :

Dim conPaye As ADODB.Connection

Set conPaye = New ADODB.Connection
conPaye.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\BIRS6\Tax.mdb'"

conPaye.Execute "CREATE TABLE Paye(" & _
"AssessmentYear Date Number(6),TaxPayerID Smallint(10),Name VarChar(50),Address varchar(50),TownVillage varchar(50),State varchar(50), " & _
"BasicSalary Currency,HousingAllowance Currency,TravellingAllowance Cuurency,UtilityAllowance Currency,MealSubsidyAllowance Currency,EntertainmentAllowance Currency,LeaveGrant Currency,OtherAllowances Currency,TotalAllowances currency,GrossPay Currency, " & _
"PersonalAllowance Currency,ChildrenAllowance Currency,DependantRelativesAllowance Currency,HousingReliefAllowance Currency,TransportReliefAllowance currency,UtilityReliefAllowance Currency,MealSubsidyReliefAllowance Currency,EntertainmentReliefAllowance Currency, " & _
"LifeAssurancePolicy Currency,LeaveGrantReliefAllowance Currency,DisabilityAllowance currency,PensionFund Currency,NationalHousingFund Currency,GrossTaxFreePay Currency,TaxablePay Currency,AnnualTaxPayable Currency,MonthlyTaxPayable Currency);"

Set conPaye = Nothing
End Sub

After writing the above statement I receive this compile error:

'Syntax Error in CREATE TABLE statement'.

I tried several times to detect my mistake but could not.Please kindly point out what is wrong with the above code.
Thank you in advance.
Akinyemi.

Hi. In this part of your statement:

AssessmentYear Date Number(6),TaxPayerID Smallint(10),Name VarChar(50),Address varchar(50),TownVillage varchar(50),State varchar(50), " & _
"BasicSalary Currency,HousingAllowance Currency,TravellingAllowance Cuurency,UtilityAllowance Currency,MealSubsidyAllowance 

You have you have a spelling error with currency and can you tell me what the data type is for AssessmentYear?

[Akinyemi]

Thanks for your interest in helping me.

In Nigerian tax system, Assessment is based on Calender year: 2005 Assessment Year; 2006 Assessment Year etc.
The user of my tax software is expected to enter in the Assessment Year Textbox "2005" or "2006" . In 2007 the user will be expected to enter 2007 in the Assessment year textbox. That is how our tax system is.

I used Integer. It didn't work. Then I used Number. Yet it didn't work.

Thank you.

Akinyemi

[Killer42]

I'm not familiar with the CREATE TABLE statement, but if the syntax for each field is name followed by type, then "AssessmentYear Date Number(6)" doesn't seem right. If the field name has a space in it, don't you need square brackets around it, or something?