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!
7 8135
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
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!
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
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.......... ..
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
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Stephen Ferg |
last post by:
There are a couple of broken links on the Python Eggs site:
http://www.python-eggs.org/links.html
I'd like to reach the maintainer and report them, but there's no
information about the maintainer on the page.
Does anyone know who the maintainer is, and how to reach him/her?
|
by: Andreas Lauffer |
last post by:
I changed from Access97 to AccessXP and I have immense performance
problems.
Details:
- Access XP MDB with Jet 4.0 ( no ADP-Project )
- Linked Tables to SQL-Server 2000 over ODBC
I used the SQL Profile to watch the T-SQL-Command which Access ( who
creates the commands?) creates and noticed:
|
by: Sam |
last post by:
I have developed a small database which is setup with the front end and
backend files.
There are 12 linked tables withe the datafile located on our server.
Now the boss wants to be able to take a laptop offsite and still have
access, (even if read only) to the main data file (to be able to add records
in 1 table would also be useful).
I would like to be able to get the user to press a command button which
|
by: ARobi |
last post by:
I have developped an Access database with a lot of coding. The size of
the database without data is about 5 meg.
I am ready to copy the database to a client PC which already has a legal
version of Access installed.
1.I am used to work with software such has C++ which basically save the
program in many files but I notice that Access VBA save all data into a
one big file. Any way I can brake it in multiple files?
2. If I make a change in...
|
by: Scotty |
last post by:
I have a database which is made up of several tables "contacts",
"coaching", "clubs" etc. The Contact and coach tables are linked by an
"ID" field. The Contact and clubs table are linked a by a C_ID field.
I have a have a form to input new records and I need to tnter the "ID"
value int 2 tables to maintain the link.
As a result of a previous attempt I have contacts as a main form with
coach as a sub form. Lookin good...
I now have a...
| |
by: ET |
last post by:
Hello friends,
did somebody try this, is it possible to change Linked Table's links on
run time?
Lets say, database in mde format opens, and you need to change location
of the datafiles for linked tables.
You click a button on a update form, and explorer window pops up,
allowing you to specify location of the file.
|
by: ET |
last post by:
I don't know whats the problem, but after I added functions
to first verify, then relink linked tables if not found,
now I can't convert that database to MDE format.
I can split the database, but can't convert
part of the database with forms, reports, queries to MDE format.
Can somebody advice on this?
References, in the order, from the top:
|
by: aaron.m.johnson |
last post by:
I have an application which contains an Access database with linked
tables that point to another database within the application. The
problem I have is that when the user installs the application, I need
to update the table links so that the paths are correct for the install
directory. Is there an easy way to accomplish this?
I'd really like to avoid duplicating the data in the linked tables, but
if updating the links is too much work,...
|
by: majortool |
last post by:
I work for a company where I do quality assurance. We get a
spreadsheet every week of the curent employees where I work. What I
need to do is import this spreadsheet every week and update my
tables.
Access will not allow me to create good relationships between other
tables and the linked table. Also, if I import the table it works
fine; however, the second time I import into the same table it gives
me a non-descript error message....
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |