Results 1 to 7 of 7
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    156

    Local Tables vs Linked tables

    New to SQL server, (so i apologize if this is a really stupid question)



    have my db split and the backend loaded into SQL Server with all of the tables linked.

    now my question is, if I want to add some tables and play around with adding an new feature/whatever to the db
    Can I just create the tables in access (which would be local tables) then when I have whatever forms and reports built around the tables, can I then just "Send" the new tables that I have to my db in SQL server and re link them?

    or do I absolutely have to create the tables in SQL Server first and then build my forms/qry/rpts around the linked table?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,769
    If you are developing/playing around, you could do everything in Access -especially if you are working (at least partly at home )where you may not have SQL Server. Easy to adjust, modify, etc. Then after your done playing, finalize logic and move tables to SQL server, check/vet/adjust any logic....
    Or, if you have access to SQL server and Access you can do linking early on and continue playing.

    I think any combination would work, the question is --Is there some approach that offers advantages to you - makes life easier?? That's probably the approach to use/choose.

    I have often done things all in Access, then moved tables to Oracle backend.
    Last edited by orange; 04-17-2021 at 08:21 AM. Reason: spelling

  3. #3
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    156
    Orange - Thank you for the input.

    Right now it is still all on my laptop Access and SQL Server (with the backend)
    my next step once I get a little more comfortable with what I have going on is to migrate to Azure.

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,444
    depends on what playing around you are doing. Tables in one backend cannot relate to tables in other backends so you can suffer from performance issues with queries

    And if you are using sql server/azure as a backend I would expect you to be making good use of passthrough queries and stored procedures for performance reasons - these cannot reference local tables.

    but to answer your question you can do what you want although I suspect you would import the local tables to sql server/azure rather than export from access.

    Personally, assuming you have production copy in the backend I would copy it to a development/testing environment which matches the production environment

  5. #5
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    830
    Creating your tables/views on SQL server is a great way to get to know the database software, so I would advise to play around with all possibilities of the SSMS, creating some schema's, tables, views, procedures, ... . Try out the security settings and learn the differences between access data types and SQL data types. Once your confident with your new environment, a lot of new possibilities will pop up.

  6. #6
    keviny04 is offline Competent Performer
    Windows 7 64bit Office 365
    Join Date
    Apr 2015
    Posts
    114
    If the ultimate goal is a new table on SQL Server, then it's better to create it directly there. Any other way is just for testing, playing around, etc., like you said.

    You can create a table on SQL Server with a passthrough query in Access:

    Code:
    CREATE TABLE TestTable_Persons (
        PersonID int,
        LastName varchar(255),
        FirstName varchar(255),
        Address varchar(255),
        City varchar(255)
    );
    Or you can do it in SQL Server Management Studio if you prefer a graphical user interface.

  7. #7
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    830
    If you create your table using a script in a pass-through query, don't forget to add the primarey key, example script with PK added:


    Code:
    USE [TSQL_NGA]
    GO
    
    
     SET ANSI_NULLS ON
    GO
    
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    CREATE TABLE [dbo].[tblTest](
        [testID] [bigint] IDENTITY(1,1) NOT NULL,
        [testNr] [int] NOT NULL,
        [testText] [nvarchar](50) NULL,
     CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED 
    (
        [testID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

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

Similar Threads

  1. Replies: 6
    Last Post: 04-18-2018, 06:56 AM
  2. Replies: 17
    Last Post: 04-09-2018, 04:39 PM
  3. Replies: 6
    Last Post: 02-16-2016, 09:20 PM
  4. Replies: 1
    Last Post: 12-11-2013, 01:18 PM
  5. Replies: 4
    Last Post: 11-22-2013, 11:20 AM

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