Results 1 to 10 of 10
  1. #1
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121

    Temporary Tables

    I dont have permission to create table on server but when I tried to create temp table it got created.

    Create table polist (PO nvarchar(20))


    insert into #polist values ('4022742493')
    select * from #polist

    I want that table in MS access so that I could append all my POs in temp table but I dont know where it got created. Even When I run pass through query in access it give me error that invalid object. When I run it on sql it shows me one po.

    where I am doing wrong.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly is your set up? FE/ BE
    Is the table in your front end?
    If you look in your Navigation area, does it show up in your Tables?

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Actually, 'temp' table should probably be permanent and the records in the table would be temporary. That's how I do it because creating and deleting tables is making design changes whereas adding and deleting records is not.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by orange View Post
    What exactly is your set up? FE/ BE
    Is the table in your front end?
    If you look in your Navigation area, does it show up in your Tables?
    Front End is SAP and BE SQL server. I have list of around 750 POs which I want to retreive the data. But my BE table is very huge I cant link with table. It freeze my computer is there any way to retrieve data.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Does your IT/DBA allow you to have ReadOnly Access to the BE? If not, why not? Do they offer a facility to provide data to you for your job/duties?
    What options does your organization offer for you to process/interact with the data you need?

    How does SAP deal with Access?

  6. #6
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by orange View Post
    Does your IT/DBA allow you to have ReadOnly Access to the BE? If not, why not? Do they offer a facility to provide data to you for your job/duties?
    What options does your organization offer for you to process/interact with the data you need?

    How does SAP deal with Access?
    Yes read only access. I can create temp tables on server. SAP is front end and SQL server is BE. I linked sql table via odbc connection.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Temporary table declared as #Tablename exists only for your current SQL Server session, it is not a table existing in SQL Server database. So I don't belive you can link it to Access front-end, or access it through query outside of SQL Server session.
    There is another type of temporary tables , ##Tablename, which exist for multiple SQL Server sessions. But I'm afraid them you also can access only from SQL Server sessions.

    For your case, best solution will be a real SQL table. When you want to use it only for temporary data, you can keep it empty usually, and fill it with data only, when there is a need for this.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    How would that SQL table be managed with multiple users?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    When you want to have user-specific data in such table, and for different users at same time to boot:

    1. Have this table in Access front-end. This means of-course, that it will not be accessible for SQL Sever, i.e. you can't use it in pass-through queries, but only in queries running in front-end;
    2. Have a field in this "dummy" SQL table identifying the user (p.e. users login). Every user add's info marked with its login, can read only this info, and can delete only info marked with his login.

  10. #10
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    can't you ask your DBA to create a view you can update that shows only the records you want and link to this view from Access?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. remove a temporary variable
    By Jen0dorf in forum Access
    Replies: 5
    Last Post: 08-08-2017, 02:50 PM
  2. Display a temporary message
    By mmaule in forum Access
    Replies: 2
    Last Post: 02-16-2015, 02:41 PM
  3. Replies: 1
    Last Post: 03-07-2014, 11:37 AM
  4. Temporary Tables
    By ajzpop in forum Access
    Replies: 5
    Last Post: 10-09-2013, 04:30 PM
  5. Temporary tables
    By MDB in forum Forms
    Replies: 3
    Last Post: 08-14-2011, 12:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums