Maintaining links in linked tables

[ChristinaB]

I'm trying to create a simple reporting tool linked to a sql server.

I create a new database,
create a new link table, of type ODBC,
select my datasource DSN= quantum_db.dsn,
click ok,
enter the user name and password,
select the table I want,
and open it.

So far, everything is fine. All the data is there and looking good.

Now I close the table, and close access.

Now I open my database in access,
double click on my link table,
and after it thinks for a while, I get
Could not execute query; could not find linked table.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.ARCHIVE_LO G'.(#208)
When I click ok, I get
Microsoft Office Access can't open the table in Datasheet view.


So then I go to the linked table manager, and select the datasource again and refresh the table.
The message box says that everything went fine.
But when I double click on the table, I get
Could not execute query; could not find linked table.
[Microsoft][ODBC SQL Server Driver][SQL Serveer]Invalid object name 'dbo.ARCHIVE_LO G'. (#208)
[Microsoft][ODBC SQL Server Driver][SQL Serveer]Statement(s) could not be prepared.(#8180 )

If I never never click ok, it will keep popping up.
When I click ok, the table opens, but #Name? is in every cell of every record.


What am I doing wrong here? If I can connect once, why can't I connect again? I really need some advice on this because I've got 3 projects that are supposed to be pretty small that my work wants me to do, and this is turning them into big projects.

Thanks!

[Jim Doherty]

I'm trying to create a simple reporting tool linked to a sql server.

I create a new database,
create a new link table, of type ODBC,
select my datasource DSN= quantum_db.dsn,
click ok,
enter the user name and password,
select the table I want,
and open it.

So far, everything is fine. All the data is there and looking good.

Now I close the table, and close access.

Now I open my database in access,
double click on my link table,
and after it thinks for a while, I get
Could not execute query; could not find linked table.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.ARCHIVE_LO G'.(#208)
When I click ok, I get
Microsoft Office Access can't open the table in Datasheet view.


So then I go to the linked table manager, and select the datasource again and refresh the table.
The message box says that everything went fine.
But when I double click on the table, I get
Could not execute query; could not find linked table.
[Microsoft][ODBC SQL Server Driver][SQL Serveer]Invalid object name 'dbo.ARCHIVE_LO G'. (#208)
[Microsoft][ODBC SQL Server Driver][SQL Serveer]Statement(s) could not be prepared.(#8180 )

If I never never click ok, it will keep popping up.
When I click ok, the table opens, but #Name? is in every cell of every record.


What am I doing wrong here? If I can connect once, why can't I connect again? I really need some advice on this because I've got 3 projects that are supposed to be pretty small that my work wants me to do, and this is turning them into big projects.

Thanks!

More info needed please.... What version Access you working, what service pack... you have what version MDAC....

in the meantime...... try creating an ADP Project file ( I know you might want to remain with MDB format) but this is merely to check that you do not have REconnection issues to SQL server using that method. .ADP files connect via UDL (Universal Data Link as opposed to ODBC)

File.... New .....Project File Existing.save as .adp file then enter your user name password follow the prompts select your database name etc and see if you see the table in the Access window eventually close and reopen the ADP file and then close and reopen again to see if you have no problems viewing the table

Jim

[ChristinaB]

I'm using Access 2003, sp2. When I look in the registry, I get 2.81.1117.0.

I tried connecting using the ADP, and I can reconnect just fine. You're right though, I actually would like to keep to MDB if possible. However, its nice to see my data for once!

[Jim Doherty]

I'm using Access 2003, sp2. When I look in the registry, I get 2.81.1117.0.

I tried connecting using the ADP, and I can reconnect just fine. You're right though, I actually would like to keep to MDB if possible. However, its nice to see my data for once!

The fact that you could see the tables in the first place when you initially linked via ODBC them tells me you have SQL server SELECT permissions on the tables at the very least.

the ODBC driver is having difficulty reading the specific table 'dbo.ARCHIVE_LO G'. Its a process of elimination I am afraid to try and track this down either on the permissions side or the driver side.

Sometimes it can be as simple as installing the latest MDAC drivers

Regards

Jim

[ChristinaB]

Is there any reason that I shouldn't be making this report tool as an .adp?

I've just never dealt with this before, but if it works.......... ..

[Jim Doherty]

Is there any reason that I shouldn't be making this report tool as an .adp?

I've just never dealt with this before, but if it works.......... ..

I personally use much prefer the ADP format for working with SQL server and I,ve been using MDBs for yeeeears. I see no reason for NOT using the ADP format for producing your reports with a possible exception of crosstab reports which you 'can' do but its not quite so easy (unless of course you have SQL server 2005 which goes some way to addressing that using pivot etc).

Processing is mostly centralised on the server as opposed to the client, your data is centralised making it available for other things should you so require it and the list goes on.

There are fundamental differences in both formats, one relies on what is called the JET engine thats Access standard 'brain' if you like where, not always but mostly processing is done on the client machine whereas the other (ADP format) communicates directly with the SQL Server using a standard UDL file connection from the client machine with SQL server returning only those records from the server that you need. This is for me absolutely the way to go when on a network with multiple users.

Tables, Queries, views and stored procedures are all centralised on the SQL server making for easier mainenance. The ADP merely looks at them making alterations if so required from the client in order to return your data.

You currently have a connection problem with ODBC it seems and none with UDL

For me ADP has provided for much more stability on a network particularly across a WIDE area network with multiples of concurrent users hitting on the database, never experienced corruption problems, a horrible feature of MDB (it which never intended for more than a few users using at any one time), much quicker etc etc etc.

In fairness to advocates of the MDB You can STILL communicate with SQL server with an MDB using what is called 'pass through' queries. But I simply say if the MDB were so easily successful and seemless why did Microsoft deploy the ADP format strongly advocating useage when working with server supplied records.

Access 2007 on the other hand, prefers the MDB format (arguably a step backwards some say but in actual fact JET was intended for Access when manipulating data in code on the client machine) I'm afraid to say that sometimes its simply a case of whose football team are you on as there are passionate supporters for both and fears from both camps as to whether Microsoft are going to DUMP one or the other formats as they did Visual basic 6

Myyyyyyyyyy this can be a techie debating parliament can't it

You simply want to get the job done!!! I can see that. I suggest you have a go creating some simple reports using the ADP investigate it, if you have the time that is, have a look at how to create a query (view) etc and eventually a stored procedure. Change or new ways of doing things is never for the faint hearted but then again the same was said the first time one picked up a mouse.

The language is a consideration with the ADP you will be using what is called TRANSACT SQL which is SQL servers language to create your queries or VIEWS as they are called and the same on STORED PROCEDURES. If you are merely a standard user you might say phew! what a learning curve but if you are power user (report creator,query maker etc etc) you might well appreciate it and certainly if you have a penchant overall for data handling on the technical side then knowing SQL server language is a positive advantage.

The differences precisely one could write a novel about, what I suggest you do is that you google 'Access versus SQL Server' or 'MDB versus ADP' there are a mirad of topics out there debating the differences and preferences.

The short answer to your question......Y es....your reporting tool can be done with the ADP

I hope this helps you


Jim

[ChristinaB]

Wow. That absolutely helped me. From what you describe, I think that ADP could actually be a better option for me. (I prefer to simply type in a SQL query rather than try to 'trick' access into getting the correct info for me.) And this thing will be used by many users, etc, etc.

Thanks for all the info/direction.

-c

[Jim Doherty]

Wow. That absolutely helped me. From what you describe, I think that ADP could actually be a better option for me. (I prefer to simply type in a SQL query rather than try to 'trick' access into getting the correct info for me.) And this thing will be used by many users, etc, etc.

Thanks for all the info/direction.

-c

You're very welcome Christina I'm glad it helped you

Jim