Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2006
    Posts
    26

    Unanswered: creating Database Table

    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.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    TravellingAllowance Cuurency?
    Inspiration Through Fermentation

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What Ol' Red here is questioning is that you have column names with embedded spaces necessitating the use of brackets around each column name ... Ex: [BlahBlahBlah BlahBlah]
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Oh, and you might want to verify your count of columns cause Access allows a MAX of 255 columns per table ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You're right, Mike... I did look for embedded spaces in his column names,
    but if there were any, I missed them. Therefore, the use of brackets should be optional.

    Maybe this is just something I've never done before, but I didn't know you could execute a "Create Table" sql statement against an Access database. I thought you had to use CreateTableDef.

    I assume it's Access because the file name ends in .mdb, but then if it is an Access table, your data types are wrong, too.
    Last edited by RedNeckGeek; 01-12-07 at 10:20.
    Inspiration Through Fermentation

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by RedNeckGeek
    You're right, Mike... I did look for embedded spaces in his column names,
    but if there were any, I missed them. Therefore, the use of brackets should be optional.

    Maybe this is just something I've never done before, but I didn't know you could execute a "Create Table" sql statement against an Access database. I thought you had to use CreateTableDef.

    I assume it's Access because the file name ends in .mdb, but then if it is an Access table, your data types are wrong, too.
    Sure can ... Also, embedded spaces (or for that matter other "special" characters) REQUIRES bracketing in order to work ...


    Check the 1st column ... Is there supposed to be a space before the word "Date"?

    Altho there is some incorrect types ... No VARCHAR - use TEXT, No SMALLINT - Use INTEGER

    I'll have to check on Number (with a size/precision qualifier?)
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Interesting.... learn something new every (other) day!
    I'll probaly never have to use it, but cool none-the-less.

    What I'm saying is akinyemi is not using special characters or spaces, so akinyemi doesn't have to use brackets.

    I suspected that when they fixed the one syntax error I found, then they would see a new error about data types, but...

    I ran this line of code against my Access DB

    CurrentDb.Execute "create table tmp(tmpfld varchar(5))"

    and it ran fine. Created the table, with a TEXT field of size 5.
    Inspiration Through Fermentation

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    As you can tell, I'm bored today....

    I got it to work, but only after changing it to

    Code:
    "CREATE TABLE Paye(" & _
    "AssessmentYear Date,TaxPayerID Smallint,Name VarChar(50),Address varchar(50),TownVillage varchar(50),State varchar(50), " & _
    "BasicSalary Currency,HousingAllowance Currency,TravellingAllowance Currency,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);"
    note I dropped the "Number(6) after date, and changed SmallInt(10) to SmallInt.
    Inspiration Through Fermentation

  9. #9
    Join Date
    Jan 2007
    Posts
    1

    Create table with variable name?

    Hello Sir,
    I am fresher for VB, I want to create table with name of any variable.
    Like just select an item from ComboBox and I have to create table with ComboBox.Text.
    How can i dot it?

  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by Shailesh Mishra
    Hello Sir,
    I am fresher for VB, I want to create table with name of any variable.
    Like just select an item from ComboBox and I have to create table with ComboBox.Text.
    How can i dot it?
    build a SQL string using the combo box.text value, open a connection to the database, and execute the string.

    Code:
    Dim strSQL as string
    
    ' build the CREATE TABLE command in a string
    strSQL = "CREATE TABLE " & combo1.text & " ....."
    
    ' open the database connection
    cn.Open YourConnectStr
    
    ' and execute the command you previously built
    cn.Execute strSQL
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •