Path/File Access Error

[Guest]

I tried to simplify my code because my select is much longer than this, but
this is basically what I'm trying to do.

Open a recordset that I loop through looking for various items and updating
a table. This worked when I was 100% Access, but now my tables are in SQL
Server. I'm getting the message "Path/File Access Error".

I'm fairly new to SQL Server and using Access as the front end. It's been
very frustrating for me to say the least. Any assistance or thoughts would
be appreciated!

Dim db As Database
Dim strSQL As String
Dim rs As Recordset

Set db = CurrentDb()

strSQL = "SELECT "dbo_Anesth.* FROM dbo_Anesth "
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

 

[Tim Ferguson]

This worked when I was 100% Access, but now my tables are in SQL
Server. I'm getting the message "Path/File Access Error".
....

strSQL = "SELECT "dbo_Anesth.* FROM dbo_Anesth "

I don't recognise the error, but several things change when you move to
SQL server. Have you checked:

presence of wildcards; ANSI uses % and _ instead of * and ?

access paths: dbo_Anesth is a really strange name for a table. Try
Anesth instead, or dbo.Anesth if it still complains. You don't need
the the table name in the field list except to disambiguate names
that appear more than once.

delimiters: Jet-style #yyyy-mm-dd# dates are out, 'yyyymmdd' are in
(with the quote marks); strings always use 'single quotes' and never
double ones; use a N'string' as a variable-length constant.

Apart from the illegal " in your example, which kind of clinches that you
have not tested this actual text, I would start with something like

strSQL = "SELECT * FROM Anesth"

and build up from there until you get the error again.

Just one thing: you do have the path to the database correct, and
appropriate privileges, don't you?

HTH

Tim F

 

[Guest]

Tim,

Thank you this is great information. I do have the ODBC connect set to
remember the password and I have all rights. I have another part of the
application where I'm writing to the database and after loosing a lot of
sleep over it...seems to be working.

Sorry about the code, you're right I didn't test it...I was trying to strip
out all the extra fields, etc., but will remember that for the next time.
I'll give your suggestions a whirl.

Thanks again,
Sash

 

[Guest]

Tim,

I set a bunch of breakpoints and found the problem which was unrelated to my
recordset. I have the following to write to a file and after moving the DB,
forgot to change the drive. Boy is my face red!

Open "k:\anes.txt" For Output As #1

Sash

 

[Tim Ferguson]

Boy is my face red!

<g> Happens to us all...

Seriously, upsizing from access to SQL server is rarely problem free. I do
quite like ADPs, although I know other people round here differ on that.

Remember to look for "hidden" SQL in rowsources for listboxes,
recordsources in forms, subforms, reports etc.

Best wishes


Tim F