create table command in access

[tar28un]

Hi there,

I am trying to create a small aplication which first ask the user to select some excel files which he wants to import and then import those excel files into access. My problem is that when the file gets imported into access, it dont have the proper datatypes. So what I want to do is create a table in access first and set the datatypes of the fields to what I want them to. I am trying to use the create table command but I am not able to do so...The command I am using is:
str1 = "Create table tablename (URN int,Forename varchar(255),Surname varchar(255),Date_of_birth date,House_name varchar(255),House_number varchar(255),Road varchar(255),Postcode varchar(255),Number_of_sessions_claimed_per_week int,Number_of_weeks_claimed_max_14_weeks int,Total_number_of_sessions_claimed_max_55_sessions int,SEN_Funded int)"

docmd.runsql(str1)

 

[PHV]

What is the problem ?
I've successfully ran your runsql command in my ac2003 database.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tar28un]

For the first line I get the error "End of statement expected"

and for the second it says"Expected select, delete,insert.........."

 

[JerryKlmns]

This should be an one line statement in a well-formatted SQL code

str1 = "CREATE TABLE tablename " & _
"(" & _
"URN INTEGER, " & _
"Forename TEXT(255), " & _
"Surname TEXT(255), " & _
"Date_of_birth DATE, " & _
"House_name TEXT(255), " & _
"House_number TEXT(255), " & _
"Road TEXT(255), " & _
"Postcode TEXT(255), " & _
"Number_of_sessions_claimed_per_week INTEGER, " & _
"Number_of_weeks_claimed_max_14_weeks BYTE, " & _
"Total_number_of_sessions_claimed_max_55_sessions BYTE, " & _
"SEN_Funded INTEGER" & _
")"

 

[tar28un]

Is there any way of using a variable intead of actual tablee name. If yes,then how

 

[JerryKlmns]

Dim myTableName As String
Dim str1 As String

myTableName ="KOYKOYPOYKOY"

str1 = "CREATE TABLE " & myTableName & " " & _
"(" & _
"URN INTEGER, " & _
"Forename TEXT(255), " & _
"Surname TEXT(255), " & _
"Date_of_birth DATE, " & _
"House_name TEXT(255), " & _
"House_number TEXT(255), " & _
"Road TEXT(255), " & _
"Postcode TEXT(255), " & _
"Number_of_sessions_claimed_per_week INTEGER, " & _
"Number_of_weeks_claimed_max_14_weeks BYTE, " & _
"Total_number_of_sessions_claimed_max_55_sessions BYTE, " & _
"SEN_Funded INTEGER" & _
")"

 

[PHV]

A safer way:
str1 = "CREATE TABLE [" & myTableName & "] " & _
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886