Openquery with a sub-sql referening sqlserver table.

Hello.
I need to do a openquery to a linked server, and get record with id no in a sub select pointing to a table stored in SQLServer.
I have something like this:

select * into tmptable
from openquery (select * from linkedserverTable where id not in (select distinct(id) from sqlserverTable))

How to make sqserverTable not pointing to linked server, but sqlserver ?

Rgds

JCselect *
into tmptable
from openquery (linkedserver,'select * from linkedserverTable')
where id not in (select distinct id from sqlserverTable)

Open File From Report?

Hello,

I am trying to open a file on a network drive from a report. The file's location that I need to open is stored in the db for each record. I have tried using javascript to open a new window using theFile:/// prefix. I have been able to open a new windows and navigate to a URL in this fashion by using a javascript window.open call and placing it in the navigation tab of the textbox. When I try this with a file, however, I get an access denied message. I was thinking to try opening the file by using the System.Diagnostic.Process.Start() method from code, but I'm not sure how to wire that up to the OnClick event of the textbox on the report. Does anyone have any ideas?

HI, ski081:

You can have a try to use the UNC path in the Navigate to the URL in the new windows:

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

HI, ski081:

We are marking this issue as "Answered". If you have any new findings or concerns, please feel free to unmark the issue.
Thank you for your understanding!

Open file from report?

Hello,

I am trying to open a file on a network drive from a report. The file's location that I need to open is stored in the db for each record. I have tried using javascript to open a new window using the File:/// prefix. I have been able to open a new windows and navigate to a URL in this fashion by using a javascript window.open call and placing it in the navigation tab of the textbox. When I try this with a file, however, I get an access denied message. I was thinking to try opening the file by using the System.Diagnostic.Process.Start() method from code, but I'm not sure how to wire that up to the OnClick event of the textbox on the report. Does anyone have any ideas?

Could you point to a web page and then use a query string parameter to redirect to the file?

cheers,

Andrew

|||Hi, Andrew. Thanks for the reply. I solved this one yesterday. It was really simple, and I can't believe I didn't try it before. I was trying to open the .doc file in a new window using javascript (window.open) with the file:\\\ handle. I finally tried just putting the UNC path into the Navigate to URL field under the textbox's properties/navigation tab. This worked perfectly. It immediately opened Word and then loaded the file. For reference, this also works with a drive letter, such as C:\test.doc.

Open a blank or new record set code needed.

I have an access 2003 Data access page published to the web that gets its data successfully from an MS SQL 2005 server. I need the "page when it opens" to display a new / blank record (not the first record in the table as it does by default) any pointers would be nice thanks Pete...Hi,

Im not sure if this is the best forum for this question as you say the page gets its data successfully from an MS SQL 2005 Database server.

Sorry to not be able to assist more.
|||

If you connect a table through a linked table to SQL Server, you will have to specify a primary key on the Access side, otherwise Access might not know how to update / insert new records.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you for your input.

I don’t want to sound confusing although I am confused! Here goes. I built the Access database with Access 2007, connecting to a 2005 SQL server. The database tables all have primary keys. The forms all function fine and I used the code in them “d0cmd on open goto newrecord” and the forms open ready for a new record concealing existing data from the users.

I then used access 2003, to create data access pages and publish them to our web site. (nice trick ha?) So far this works fine usingthe record navigator and custom built controls I can ad / edit and create new records. What I need to solve is, when I open a data access page over the internet the first record is displayed. I need the page to open and be ready to accept a new record showing all blank fields and not existing records.

The data assess page requires different coding to accomplish this than the forms and this is where I am getting lost?

|||I found the solution this was easy, open the dataaccess page in design view in access, select edit / page and set the can edit properties to false, the page opens to a blank record and will not reveal other records, just what i wanted.

Open a blank or new record set code needed.

I have an access 2003 Data access page published to the web that gets its data successfully from an MS SQL 2005 server. I need the "page when it opens" to display a new / blank record (not the first record in the table as it does by default) any pointers would be nice thanks Pete...Hi,

Im not sure if this is the best forum for this question as you say the page gets its data successfully from an MS SQL 2005 Database server.

Sorry to not be able to assist more.
|||

If you connect a table through a linked table to SQL Server, you will have to specify a primary key on the Access side, otherwise Access might not know how to update / insert new records.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you for your input.

I don’t want to sound confusing although I am confused! Here goes. I built the Access database with Access 2007, connecting to a 2005 SQL server. The database tables all have primary keys. The forms all function fine and I used the code in them “d0cmd on open goto newrecord” and the forms open ready for a new record concealing existing data from the users.

I then used access 2003, to create data access pages and publish them to our web site. (nice trick ha?) So far this works fine usingthe record navigator and custom built controls I can ad / edit and create new records. What I need to solve is, when I open a data access page over the internet the first record is displayed. I need the page to open and be ready to accept a new record showing all blank fields and not existing records.

The data assess page requires different coding to accomplish this than the forms and this is where I am getting lost?

|||I found the solution this was easy, open the dataaccess page in design view in access, select edit / page and set the can edit properties to false, the page opens to a blank record and will not reveal other records, just what i wanted.

only want one record returned per card (was "Query Help - New expert-NOT")

I only want one record returned PER card that has access to either of the doors specified in readername. My current results ARE below query

CURRENT QUERY:

Select lastname, cards.cardid,firstname,readername
from Cards, cardaccesslevels, readers
where (readername = 'Door 156B-RF Vest to Prod RF-N'
or readername = 'Door 157a-RF Vest to Prod RF - S'
or readername = 'Door 122B-WarRoom to RF'
or readername = 'Door 121-ecc to raised floor'
or readername = 'Door 154a-Hallway to UPS'
or readername = 'Door 131A-Battery Room A'
or readername = 'Door 151b-switchgear exit'
or readername = 'Door 132-battery room b'
or readername = 'Door 140B-Rec to chiller room'
or readername = 'Door 130-Hallway to Telco vendr b'
or readername = 'Door 134-hallway to telco vendr a')
and (cards.cardid = cardaccesslevels.cardid)
order by lastname
****
CURRENT RESULTS
Abdullah 497 Phakhruddin Door 121-ECC to Raised Floor
Abdullah 497 Phakhruddin Door 131A-Battery Room A
Abdullah 497 Phakhruddin Door 132-Battery Room B
Abdullah 497 Phakhruddin Door 140B-Rec to Chiller room
Abdullah 497 Phakhruddin Door 154A-Hallway to UPS
Abdullah 497 Phakhruddin Door 156B-RF Vest to Prod RF-NHow about:
Select DISTINCT lastname, cards.cardid,firstname
from Cards, cardaccesslevels, readers
where (readername = 'Door 156B-RF Vest to Prod RF-N'
or readername = 'Door 157a-RF Vest to Prod RF - S'
or readername = 'Door 122B-WarRoom to RF'
or readername = 'Door 121-ecc to raised floor'
or readername = 'Door 154a-Hallway to UPS'
or readername = 'Door 131A-Battery Room A'
or readername = 'Door 151b-switchgear exit'
or readername = 'Door 132-battery room b'
or readername = 'Door 140B-Rec to chiller room'
or readername = 'Door 130-Hallway to Telco vendr b'
or readername = 'Door 134-hallway to telco vendr a')
and (cards.cardid = cardaccesslevels.cardid)
order by lastname|||I appreicate your help. It did work. I was thrust into an admin role (my boss tells everyone I am an expert..ha) for a database and my sql experience is limited which makes my job painful right now.

Only getting one record in preview mode

I've created a new simple report and I am trying to figure out why I am only getting one page of the report when I am expecting a page per db record.

1. I have a simple query that returns 7 rows of data, 1 column 'name'. They query test good in the Data tab.

2. I have dragged and drop that column to my page on the Design tab

3. I then go to the Preview tab and I only get one page with the name on it.

What am I doing wrong - I expected one page per record from my query.

Thanks,

Patrick

Obvious question first - have you checked your group by on your report (NOT data tab)?|||Where would I find this?|||Assuming you are displaying your data in a table, then just select the table. Right click on the selected table, and check out the 'Groups' tab...