Importing a csv File

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Laura - 24 Apr 2008 14:02 GMT

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?

Reply to this Message

Laura - 24 Apr 2008 14:20 GMT

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

Reply to this Message

gsnidow - 24 Apr 2008 14:48 GMT

Laura
I can't really help you with coding in Access, but I do the same type of
thing by running code on the server. If the CSV file is stored in a location
that is accesible to the server, you can just use BCP with a format file.
Create a stored procedure, say spCSV_Insert that will bulk insert into your
table, then on your form you can have a button with DoCmd.runSQL spCSV_Insert
that will run the procedure with the BCP code. You can lookup bulk insert on
BOL for more.

Greg

>Update to the above, the message I receive actually says
>"Microsoft Jet database engine cannot find the input table or query
[quoted text clipped - 3 lines]
>
>Laura

Reply to this Message

Sylvain Lafontaine - 24 Apr 2008 17:54 GMT

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.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

> 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?

Reply to this Message

gsnidow - 25 Apr 2008 14:30 GMT

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.
>
[quoted text clipped - 18 lines]
>> do
>> not want to give them. Can anyone help me?

Reply to this Message

Laura - 01 May 2008 18:55 GMT

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
[quoted text clipped - 23 lines]
> >> do
> >> not want to give them. Can anyone help me?

Reply to this Message