MS Jet Database Engine can't find my tables

[elviajero]

Got my tables imported to my SQL server with DTS thanks to some help yesterday.

Now, I have ODBC Data Source set on my client machine with User DSN, System DSN and File DSN set to my database with a SQL server driver.

In SQL server enterprise manager within this database I have my user properties for this user set with Public checked under permissions.

Still when I open the database, Switchboard opens (from SQL server), but when I try to access other tables from my forms I get a message that Microsoft Jet Database engine can't find my tables, make sure they're not misspelled, etc.

What am I doing wrong?

 

[foxbox]

Did you configure the IUSR_<servername> user? br
Gerard
(-:

Better a known bug then a new release.


| Do it!
|
V

 

[elviajero]

Not familiar with that, so the answer would be no.

 

[foxbox]

&quot;I try to access other tables from my forms&quot; sounded as if you access the data via a web-form? Under IIS a special user is doing that: IUSR_<your servername> (it is already in your NT user list). But you must configure this user in SQL Server (and give it rights for data, sp's etc)
br
Gerard
(-:

Better a known bug then a new release.


| Do it!
|
V

 

[petermeachem]

I assume you are using access front and sql server back. If so the switchboard table is local? Have you linked to the backend tables, or how are you letting access know that those are the tables you want?

 

[foxbox]

no. i'm programming in ASP/VBScript/Javascript with SQL Server database. The program runs in the browser, and then the database is accessed via use IUSR_XXXXXXXXXX ('Internet User XXXXXXXXXX'). My classic mistake is to create tables and stored procedures and try to run them without first changing the permissions.


>how are you letting access know that those are the tables >you want?
In a program that is using ODBC you use regular SQL statements. I used to programm in dBASE/Clipper.
As soon as i write:
select * from TEST for key = 'XYZ'

i need a cup of coffee. . . .


[FOR should be WHERE]



br
Gerard
(-:

Better a known bug then a new release.


| Do it!
|
V

 

[elviajero]

Thanks foxbox, however, these aren't web forms, just generic Access forms. What you noted is good to know for the future though if I ever do that.

petermeacham, your question got me to thinking. Yes, I'm using Access front and SQL back. I originally wrote all the VB code for the event procedures on my forms using names like tbl_Locations, tbl_Users. At the time these tables were not linked SQL tables. After using DTS to import them into SQL my tables in Access are now named with a dbo_ in front of them.

So the lesson I learn is, do your link tables first before you start referring to tablenames in VB. It should be relatively easy to fix.

 

[petermeachem]

You can rename the linked tables to get rid of the dratted dbo. Then access will in general not notice that anything has changed.

 

[elviajero]

Well, I learn something new every day. SQL inserts a dbo_ in front of your table names when you import them into SQL from Access. I assumed this was a SQL naming convention and changed all the references to my table names in Access to match the new names from SQL.

I learned the above from another colleague. Only thing is, you'd have to re-name the SQL table every time you re-linked it. That probably would have been quicker though than what I did.

Thanks a for pointing my head in the right direction. You correctly mentioned my Switchbod table is local. What I need to figure out next is going to be a new post.