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).