Using SQLServer

[GPGeorge]

SQL Server Migration Assistant for Access (SSMA) is a tool that migrates tables from Access to SQL Server.

SQL Server Management Studio (SSMS) does not enter into that migration process. You do not even have to have SSMS installed to migrate the tables to SQL Server. SSMS has NO objects of it's own. It only connects to the SQL Server database so that developers can create, modify or delete objects which reside in the SQL Server database.

SSMS is relevant only to the phase after the migration is complete, the phase where you manage the tables and other objects in the SQL Server database.

• Microsoft SQL Server is an RDBMS. It's not a management tool and it's not a migration tool.
• Microsoft SQL Server Management Studio is a tool for, well, managing the objects in various SSL Server databases. It's a management tool for developers using the database.
  • There are alternative tools for this, such as DBeaver. https://dbeaver.com/docs/dbeaver/Database-driver-Microsoft-SQL-Server/
• Microsoft SQL Server Migration Assistant (for Access) is a tool that ONLY migrates tables from an Access database to a SQL Server database. It's a migration tool for moving tables from one place to another.

Three tools with three different functions, interrelated but not interchangeable.

 

[dalski]

Thanks George, oh that's surprising. I would've thought it was tightly coupled with SSMS due to TSQL. I would never have thought it independent. So shouldn't this forum be called 'Microsoft SQL Server'; not SQL Server?

 

[Minty]

You can run a T-SQL command on a SQL server without SSMS ever being installed.
Just run a passthrough query from MS Access.

To reiterate - SSMS is a no different from using Visual Studio to manipulate objects in a (MS) SQL Server database.
It's a developer tool for MSSQL Server objects.

 

[GPGeorge]

SSMS is tightly coupled to SQL Server in the sense that it is designed to work with SQL Server databases. However, it's important not to confuse the two.

SQL Server -- the objects themselves, tables, views, stored procedures, etc.

SSMS -- tools to create, modify or delete objects in the SQL Server databases.

Microsoft SQL Server is the fully qualified name of the RDMS we tend to refer to informally as SQL Server.

No other RDBMS can legitimately be referred to as "SQL Server".

I suppose one could argue that a more generic name for the forum would be possible, something like "Server-based RDBMSs".

That is peripheral to the context of this thread, though.

 

[cheekybuddha]

Migrate Access To SSMS

What does this mean?

 

[cheekybuddha]

Stored Procedures (aka SP's/ SPROC's) - ... an Action Query/ Procedure in VBA/ Procedures similar to VBA; must use EXEC to run them

You don't need to use EXEC or GO

 

[GPGeorge]

You don't need to use EXEC or GO

Please elaborate. In what context do you not need to use EXEC SP_MyAllPurposeSummary?

 

[Albert D. Kallal]

I have never even touched SSMAA

Given these facts, what would be your suggestion?

Ok, for sure then, start out using SSMS to create, manage, change, add, modify the tables.
You find SSMS not a whole lot different then ms-access. The table designer is VERY much like the Access one.
Only trick part is to setup your PK takes 2 steps, and not one like in Access.

However, above is a good/great thing, since after creating/making a few tables? You be right at home using SSMS to create and design your tables. And as noted, I find the process VERY similar to that of creating tables in Access anyway. This includes even setting up relationships.

So, I see the above as a bonus, since you become rather familiar with SSMS to create tables etc.

So, get the free edition of SQL server and SSMS installed and up and running on your computer.

To be fair, one nice aspect of SSMAA? Well, you don't have to know or think about the column types to use, you just use Access ones, and create your tables local, and then migrate them......

So, I still like the SSMAA idea, but that then means:

You have to learn SSMS - (the manager for SQL server - lets you create a database, and then create tables etc.)

You have to learn SSMAA - the migration assistant. Not hard, but still your time, and still a good day of messing around until you get comfortable with the tool.

And then of course there's ms-access......

So, lots of moving parts here to learn.

So, I would get SQL server + SSMS installed on your computer. get that working first, and then try creating a database, and a few tables....

Then, you can/could/should try creating a linked table from Access to above. All this will seem "strange" the first time around, but you fast get comfortable with this base setup....

R
Albert

 

[cheekybuddha]

Please elaborate. In what context do you not need to use EXEC SP_MyAllPurposeSummary?

Hi George,

You don't need to preface sp's with EXEC to get them to execute on SQLServer.

As an example, I have a sp called sp_describe() which mimics 'DESCRIBE TABLE ...' in MySQL - you pass the name of a table as a param and it returns field names, datatypes etc.

So in Access I create a simple pass-thru query function:

Function SPNoExecTest(strSQL As String) As Boolean

  Const CN_STRING As String = _
    "ODBC;" & _
    "DRIVER={ODBC Driver 18 for SQL Server};" & _
    "SERVER=xyzxyz.database.windows.net;" & _
    "PORT=1433;" & _
    "DATABASE=db_name;" & _
    "UID=user_id;" & _
    "PWD=my_pw"
  Dim i As Integer
 
  With CurrentDb.CreateQueryDef(vbNullString)
    .Connect = CN_STRING
    .sql = strSQL
    .ReturnsRecords = True
    With .OpenRecordset
      For i = 0 To .Fields.Count - 1
        Debug.Print Left(.Fields(i).Name & Space(20), 20);:
      Next i
      Debug.Print
      Do While Not .EOF
        For i = 0 To .Fields.Count - 1
          Debug.Print Left(.Fields(i) & Space(20), 20);:
        Next i
        Debug.Print
        .MoveNext
      Loop
      SPNoExecTest = .RecordCount > 0
      .Close
    End With
  End With

End Function

It prints out the result of the passed query to the Immediate Window.

If I pass the following SQL calling the sp with parameter 'Contacts' as the table to describe: sp_describe 'Contacts'; I get the following result:

?SPNoExecTest("sp_describe 'Contacts';")
Field               Type                Null                Key                 Default             Extra              
ContactID           INT                 NO                  PRIMARY KEY                             AUTO_INCREMENT    
CompanyID           INT                 NO                  FOREIGN KEY         0                                      
FirstName           NVARCHAR(50)        NO                                      ''                                    
Surname             NVARCHAR(50)        NO                                      ''                                    
Address1            NVARCHAR(50)        NO                                      ''                                    
Address2            NVARCHAR(50)        NO                                      ''                                    
City                NVARCHAR(50)        NO                                      ''                                    
County              NVARCHAR(50)        NO                                      ''                                    
Postcode            NVARCHAR(10)        NO                                      ''                                    
CountryID           INT                 NO                  FOREIGN KEY         230                                    
MobileNo            NVARCHAR(20)        NO                                      ''                                    
HomeNo              NVARCHAR(20)        NO                                      ''                                    
WorkNo              NVARCHAR(20)        NO                                      ''                                    
Email               NVARCHAR(255)       NO                                      ''                                    
ContactNotes        NVARCHAR(MAX)       NO                                      ''                                    
Initials            NVARCHAR(20)        NO                                      ''                                    
Title               NVARCHAR(10)        NO                                      ''                                    
DOB                 DATE                YES                                                                            
CreditLimit         DECIMAL(10, 2)      NO                                      0.0                                    
IsDriver            BIT                 NO                                      0                                      
MarketingList       NVARCHAR(1000)      NO                                      ''                                    
Shortcode           NVARCHAR(20)        NO                                      ''                                    
IsStaff             BIT                 NO                                      0                                      
CreditDays          INT                 NO                                      0                                      
ReservationReminder NVARCHAR(MAX)       NO                                      ''                                    
FromOnline          BIT                 NO                                      0                                      
Password            NVARCHAR(50)        NO                                      ''                                    
ReferrerID          INT                 NO                  FOREIGN KEY         1                                      
IsAgent             BIT                 NO                                      0                                      
PhotoURL            NVARCHAR(300)       YES                                                                            
True

I never write EXEC and I never write GO (I mainly administer SQLServer via DBeaver).

Try using one of your own sp's without the EXEC. (Of course, adapt the connection string as necessary)

hth,

d

 

[Albert D. Kallal]

Please elaborate. In what context do you not need to use EXEC SP_MyAllPurposeSummary?

Actaully, in both SSMS, or even a PT query from Access?

You don't need the EXEC in front in most cases.

So, from SSMS, then these will all work:

EXEC dbo.GetHotels

EXEC GetHotels;

dbo.GetHotels

GetHotels

And thus, even from a PT in Access, then this works:

So, the "exec" is actually not required from SSMS, or even from a Access PT query....

HOWEVER, if you going to pass parameters? Then YES, you need to include the EXEC keyword.....

And for sake of clarity? And in some cases if a table object or view has the same name?
Well, then some "troubles" can arise.

So, I'm guilty of having just used the stored procedure name, but I probably should not make a habit of doing so....


R
Albert

 

[GPGeorge]

Thank you. I think this is another case where I adopted the practice of using full syntax regardless of whether it's required or not.

 

[Albert D. Kallal]

Thank you. I think this is another case where I adopted the practice of using full syntax regardless of whether it's required or not.

What's that saying about the juice is not worth the efforts to squeeze the fruit or some such?

Typing in "exec" not going to wear out your keyboard is it?

I don't recommend leaving it out....
R
Albert

 

[DaveMD]

I think this is another case where I adopted the practice of using full syntax regardless of whether it's required or not.

Typing in "exec" not going to wear out your keyboard is it?

I don't recommend leaving it out....

I have the same thought as @GPGeorge and @Albert D. Kallal on this point. Too often a developer will come across some shorthand way to do things and ultimately it can cause more confusion than any benefit from the time saved from a couple less keystrokes. Some examples include leaving out the "AS" when aliasing a field in SQL, or leaving out "INNER" from INNER JOIN because most SQL language dialects technically treat JOIN as an INNER JOIN, or not bothering to indent your code logically. Being explicit in your syntax (and being consistent) can go a long way in having understandable, readable, and maintainable code (particularly when you are not the only developer).

 

[cheekybuddha]

Please reference Post #26.

I never said that you shouldn't use EXEC, just that you do not have to use it, which was the information given by Dalski.

That said, for me, EXEC is an unnecessary SQLServer-ism. No compiler will pick up if it's mis-typed and can lead to an avoidable point of failure if you have a keyboard slip.

Each to their own! (I also rarely use 'AS' for aliases too! ).

 

[Minty]

Personally, it all depends on whether someone else is likely to look at it later...
If they are I tend to be a bit more formal. I do like using AS in a larger query, but also don't bother when I'm debugging and it's going to get thrown away.
I also always use EXEC when calling stuff from an Access Front end, as it makes it really easy to find where the server gets involved in a complicated process.

 

[GPGeorge]

"... it makes it really easy to find ...."

I would add, "... and to make it clear what is happening."


As I get further away from the halcyon days of head down programming for clients, I find it comforting to be able to read and follow the logic of fully qualified code structures.

 

[Isaac]

FWIW, I have used Access with sql server as a back end many times, and migrated many databases FROM access be TO sql server be, and never have I touched the migration assistant. It's far from extremely difficult or impossible to do it without it. It just requires you have a solid grasp and understand of your datatypes, and study up on the best destination datatype in sql server, ask questions if needed, think of what you have allowed into access and whether it will play nice with sql server destination datatypes (or think of it from the beginning, preferably!)

 

[Minty]

The major benefit of SSMA if you are migrating a current live system is to store all the parameters and datatype mappings and easily repeat the operation, during the development phase. It also moves larges chunks of data pretty quickly and provides good reports when things don't work.

We had a sizeable and complicated system that took many months of work to migrate to SQL server and I frequently had to update to more current data for sensible end-user testing.
Importing over 1Gb of data was simple to do once it was set up and took about 40 minutes tops from start to finish.

 

[Isaac]

The major benefit of SSMA if you are migrating a current live system is to store all the parameters and datatype mappings and easily repeat the operation, during the development phase. It also moves larges chunks of data pretty quickly and provides good reports when things don't work.

We had a sizeable and complicated system that took many months of work to migrate to SQL server and I frequently had to update to more current data for sensible end-user testing.
Importing over 1Gb of data was simple to do once it was set up and took about 40 minutes tops from start to finish.

Yeah, I don't doubt it has its major benefits and conveniences. I wasn't that knowledgeable about it when I did my migrations so I did them by hand, and they all worked out in the end - but I agree with you.

I do believe that one benefit of doing it 'by hand' is it forces you to think through things at a more granular level rather than "let's just try hitting Go and see what happens", which may provide excellent reports but the former method forces you to think through and learn things as you go, which (and it may just be my personal style), I appreciated for my own learning.

 

[GPGeorge]

Having migrated Access tables to SQL Server since the early 2000's, I've had the opportunity to do so in multiple ways. Who remembers when "Export to SQL Server" was an option on the Access ribbon and before that a Menu option? Each has advantages and disadvantages, and no that is not a waffle. There really are pluses and minuses to most approaches.

I do agree that a significant advantage of SSMA is the ability to create a replicable migration project and run it over an and over during design, development, testing and final deployment. That's when "Click Go and wait for it to finish" really is a plus.