Importing a csv File

[Laura]

I have an Access adp that I need to allow the users to import data from a
csv file. I am using SQL 2000. The syntax I am using is

DoCmd.TransferText acImportDelim, , "dbo.Pack_Cell_Screen", strFilePath, True

It interprets the table name as dbo_Pack_Cell_Screen and therefore does not
recognize the table name. I tried just Pack_Cell_Screen and the user gets
a message that they do not have CREATE table rights. Which of course I do
not want to give them. Can anyone help me?

 

[Laura]

Update to the above, the message I receive actually says
"Microsoft Jet database engine cannot find the input table or query
dbo_Pack_Cell_Screen. Make sure that it exists and its name is spelled
correctly." Of course Jet can't find it because its an SQL table. How do
I make it know that its an SQL table?

Laura

 

[Sylvain Lafontaine]

You have two problems here.

First, the use of DoCmd.TransferText acImportDelim implies the creation of a
new table each time; so even if you'd a mean of specifying an already
created table named dbo.Pack_Cell_Screen - instead of Pack_Cell_Screen -
you still couldn't use this method if you don't want to give your users the
CREATE TABLE rights.

Of course, you also have the second problem that the command
DoCmd.TransferText use the old (and regular) JET engine and that with JET, a
table name cannot have a point (« . ») in it; so the name
dbo.Pack_Cell_Screen is parsed into dbo_Pack_Cell_Screen.

I might be wrong but as far as I know, I'm afraid that there is no solution
to your problem other than to use another method for importing the data from
the csv file.

 

[Laura]

Thank you both for your help.

Laura

And there you have it from the expert. I have found that my limited VBA
skills has not slowed me down any, because you can do so much by running SQL
commands directly on the server. I would really suggest you check out using
BULK INSERT with format files.

You have two problems here.

First, the use of DoCmd.TransferText acImportDelim implies the creation of a
new table each time; so even if you'd a mean of specifying an already
created table named dbo.Pack_Cell_Screen - instead of Pack_Cell_Screen -
you still couldn't use this method if you don't want to give your users the
CREATE TABLE rights.

Of course, you also have the second problem that the command
DoCmd.TransferText use the old (and regular) JET engine and that with JET, a
table name cannot have a point (« . ») in it; so the name
dbo.Pack_Cell_Screen is parsed into dbo_Pack_Cell_Screen.

I might be wrong but as far as I know, I'm afraid that there is no solution
to your problem other than to use another method for importing the data from
the csv file.

I have an Access adp that I need to allow the users to import data from a
csv file. I am using SQL 2000. The syntax I am using is

[quoted text clipped - 9 lines]

do
not want to give them. Can anyone help me?