MS Access & MS SQL Server: long time answers

I use MS ACCESS and tables linked to MS SQL SERVER tables.
There are indexes defined (they are visible in MS ACCESS too).
Sometimes DLOOKUP functions are taking long time.
Why?Are you using a standard Microsoft Access database with links to the SQL Server tables, or are you using a Microsoft Access Data Project (.adp file extenstion)?

If you are linking to SQL server, MS Access will do it's best to formulate your queries as "pass through", meaning only the sql text is sent to the server and only the process results are returned. If it is unable to formulate it as a pass-through query than Access must temporarily transfer all the tables involved across your network and then process the results locally. You can imagine how this could be unimaginably slow. (?)

I recommend that you convert your application to an Access Data Project if possible.

blindman|||I am not sure about ADP, but Access2K does not use pass-through for linked objects. In fact, the only way you can use pass-through is by selecting it from the query builder.|||It is standard Access 97 VBA application with use of DLOOKUP function.
The long time answers for DLOOKUP queries are not the rule.
I can use DLOOKUP about 10 times with fast answer (under 1 second) and next time I have to wait 10 seconds.sql

MS Access "Upsizing" Tools in SQL Server

Dave,
I have tried the method that you described to import an Access database and
I keep getting the same error:
Could not set up data flow connections. The conection type OLEDB specified
for connection manager (then I see what looks like a registry entry) is not
recognized as a valid connection manager type.
I have also tried creating a new Integrated Services Project and run the
import from there. I get the same error. When I try to upsize in Access I
get a report that says that the tables were not converted.
Any suggestions?
"Dave Patrick" wrote:

> Good to hear. You're welcome.
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Alex Maghen" wrote:
> | Thanks. Yeah, I get it jow.
> |
> | Alex
>
>
Looks like this should sort it.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=255347&SiteID=1
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Rob" wrote:
> Dave,
> I have tried the method that you described to import an Access database
> and
> I keep getting the same error:
> Could not set up data flow connections. The conection type OLEDB
> specified
> for connection manager (then I see what looks like a registry entry) is
> not
> recognized as a valid connection manager type.
> I have also tried creating a new Integrated Services Project and run the
> import from there. I get the same error. When I try to upsize in Access
> I
> get a report that says that the tables were not converted.
> Any suggestions?

MS Access - SQL Server Replication

Folks...
I have a client that is running MS Access 2002 for 10 people out in the field. The client wants to be able to replicate the data from MS Access 2002 to the SQL Server 2000. In addition, the client wants to have each field person only work on their own s
pecific data set during the replication. So the entire SQL Server database does not get replicated to the MS Access database. A good thing.
What would be a best practice approach to accomplish setting up the MS Access database and setting up the 'filters' only to pull the data that will be specifically worked on by the client.
I did some looking on the MS website and some other links...but of course the data is always fragemented.
Mephisto822
The easiest way of setting this up is using merge replication and dynamic
filters. For the dynamic filter there are options of HOST_NAME() and
SUSER_SNAME() functions and UDFs based on them, but I would suggest using
the -HOSTNAME agent parameter to easily partition the data according to
subscriber. This also gives you the option of using a business-related
partitioning value, rather than the computername. HTH,
Paul Ibison

MS Access - Security Timestamp.

I need a simple way of capturing who updated what in an Access database.

User wants to know for each field in each table.

I think it's to much for Access to handle.

Any ideas?

A.

This is not an Access forum. Sorry, but you might find better luck on an newsgroup somewhere.

However, you can set a default of "date()" in an Access Date/Time field. Just insert NULL into that field and you'll get the current date.

MS ACCESS - ON DELETE CASCADE Problem

I cannot execute my sql to create a table with ON DELETE CASCADE option.
Here is my sql:

CREATE TABLE Employees (Name Text(10) not null, Age number,
CONSTRAINT pkEmployees
PRIMARY KEY (Name)); <-- This is ok!

CREATE TABLE CanTake (Name Text(10) not null, Make Text(10) not null,
CONSTRAINT pkCanTake
PRIMARY KEY (Name, Make),
CONSTRAINT fkCanTake
FOREIGN KEY (Name) REFERENCES Employees
ON UPDATE CASCADE <-- MS-ACCESS says 'syntax error'
ON DELETE CASCADE
);

When I didn't include ON UPDATE CASCADE ON DELETE CASCADE in sql, it works
(but that's not what I want). Why? Could anyone suggest me?
Thank you.

--
Message posted via http://www.sqlmonster.comMSDN suggests that you need to use ALTER TABLE to add cascading DRI:

http://msdn.microsoft.com/library/d...ml/acintsql.asp

However, I know very little about Access, so I suggest you post in an
Access group to get a better response.

Simon|||Thank you.

--
Message posted via http://www.sqlmonster.com

ms access

whow to open link table in access

Hi,

refer to this answer and come back if you have additional questions:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1052764&SiteID=1

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.desql

Ms access

Basically I am a Oracle guy. Now I am developping some form in SQL server. I wana use SQL DB as back end and access as front end. I wanna all the tables in SQL server not as the Access DB. Is it possible.
I do not have knowledge of VB or VB script
What should I do? Please helpIn Access, select File, New, from the menu and choose to create a Project with either a new or existing database. A wizard will guide you through establishing a connetion with your SQL database, and you can even perform some administrative functions from Access ala Enterprise Manager. This means you will need to be carefull that you don't unintentionally alter your SQL database, and that you will need to lock down your final Access file so that users don't mess with your database.
The Access file will have a .adp extenstion (Access Data Project).|||[thanks mate it works|||No worries. G'day!
;)