How to copy empty tables & views from a production database to a new database (1 Viewer)

[nector]

Hi

After working so hard in the forest of MS Access and SQL Server Cloud and sorting out almost all the challenges like speed, parameter queries and others. Now I have realized that it is not possible to start all over again migrating tables, converting MS Access queries to SQL Server new Back End. We have six companies in the group and each company has its own subgroups meaning each company must maintain its own databases with its subgroups.

Is there a way to simply copy the already worked out empty tables and Views in SQL server from an existing database to a brand-new database? Any short example will help.

Lastly, I want to use the VBA code below to relink the 120 tables on click event, for example I have about 120 tables in the current database, now the other remote users do not have Full MS Access, they are using MS Access Runtime which means that they will not have the inbuilt Link Manager to refresh the tables after installing of the ODBC either 32/64 BITs. Below is the code in question:

Private Sub Form_Open(Cancel As Integer)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = [ODBC] = "DRIVER=[SQL Server Native Client 11.0] " & _
"SERVER=Mwiinde\SQLExpress;DATABASE=Accounts;Trusted_Connection=Yes"
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing
Set dbs = Nothing
End Sub

The above code appears to corrupt my application unless there is something I'm missing in it. For example, the size of my application is 23mb, if I use the above code then it grows to 70 mb. That is why I think I'm missing something here.

 

[Josef P.]

Do I understand you correctly: you want to transfer the structure of the SQL Server tables into an empty database?
You can script the entire database in SQL Server Management Studio.
Also check out Azure Data Studio. It's not just for Azure. It's handy for comparing and synchronizing databases.

 

[CJ_London]

Long time since I used it but in sql server you can create a script to create your tables, apply indexing and relationships and perhaps do some initial table population. This is stored as a text file which can then be loaded to a completely different server if required. It is basically a list of sql queries.

edit: - beaten to it

edit 2: as far your code is concerned

tdf.Connect = [ODBC] = "DRIVER=[SQL Server Native Client 11.0] " & _
"SERVER=Mwiinde\SQLExpress;DATABASE=Accounts;Trusted_Connection=Yes"

at best that will return a boolean value, think you have the first " in the wrong place and an extra = and perhap missing a ; before SERVER

and why do you say you are using sql cloud when your connection is using sqlexpress?

 

[nector]

That was just an example the cloud one looks like below:

ODBC;Description=Mrtectorprime;DRIVER=SQL Server;SERVER=s2126.winhost.com;UID=DB_157828_accounts_user;PWD=Chrfstyh#6014;DATABASE=DB_177789638_accounting

 

[Pat Hartman]

We have six companies in the group and each company has its own subgroups meaning each company must maintain its own databases with its subgroups.

Is this because your schema is wrong or because for security, each company needs its own table?

As the others mentioned, this is pretty easy with SSMS. You just pick the option to write a script that creates all the objects. You specify the target database and all the objects except data are recreated in the new database when you run the script. I've never done this with data but if the data is already in the tables and you need to segregate it, then you will need an append query for each table that select company "x" data from the original table.

Then you have to go back and delete the data for all but one company in the original database.

the size of my application is 23mb, if I use the above code then it grows to 70 mb.

Relinking creates bloat. Just compact the FE when you are done.

If you use a DSN then the tables do not have to be relinked for each company. Each company has a DSN created that points to the correct database and Access is cool with that.

 

[nector]

Ok thanks NoWonder the server was rejecting access because the already existing ODBC

Private Sub CmdConnectTo_Server_Click()
 Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim strServer As String
    Dim strDB As String
    Dim strTable As String
    Dim strConnect As String
    Dim strMsg As String
    
On Error GoTo HandleErr

    ' Build base authentication strings.
    
        ' Automatic login credentials in the code
        
           strConnect = "ODBC;Description=Neecctprprime;DRIVER=SQL Server;SERVER=s247289.winhost.com;UID=DB_155528_accounting_user;PWD=Chrislest#2023;DATABASE=DB_15658789D_accounts"
        ' SQL Server login
  
    
    ' Get rid of any old links.
    Call DeleteLinks
    
    ' Create a recordset to obtain server object names.
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
    If rst.EOF Then
        strMsg = "There are no tables listed in tblSQLTables."
        GoTo ExitHere
    End If
    
    ' Walk through the recordset and create the links.
    Do Until rst.EOF
        strServer = rst!SQLServer
        strDB = rst!SQLDatabase
        strTable = rst!SQLTable
        ' Create a new TableDef object.
        Set tdf = db.CreateTableDef(strTable)
        ' Set the Connect property to establish the link.
        tdf.Connect = strConnect & _
            "Server=" & strServer & _
            ";Database=" & strDB & ";"
        tdf.SourceTableName = strTable
        ' Append to the database's TableDefs collection.
        db.TableDefs.Append tdf
        rst.MoveNext
    Loop
    
    strMsg = "Tables linked successfully."
    
    rst.Close
    Set rst = Nothing
    Set tdf = Nothing
    Set db = Nothing
    
ExitHere:
    MsgBox strMsg, , "Link SQL Tables"
    Exit Sub

HandleErr:
    Select Case Err
        Case Else
            strMsg = Err & ": " & Err.Description
            Resume ExitHere
    End Select
End Sub
Private Sub DeleteLinks()
' Delete any leftover linked tables from a previous session.
Dim tdf As DAO.TableDef
On Error GoTo HandleErr
For Each tdf In CurrentDb.TableDefs
With tdf
' Delete only SQL Server tables.
If (.Attributes And dbAttachedODBC) = dbAttachedODBC Then
CurrentDb.Execute "DROP TABLE [" & tdf.Name & "]"
End If
End With
Next tdf
ExitHere:
Set tdf = Nothing
Exit Sub
HandleErr:
MsgBox Err & ": " & Err.Description, , "Error in DeleteLinks( )"
Resume ExitHere
Resume
End Sub

 

[Isaac]

Hi

After working so hard in the forest of MS Access and SQL Server Cloud and sorting out almost all the challenges like speed, parameter queries and others. Now I have realized that it is not possible to start all over again migrating tables, converting MS Access queries to SQL Server new Back End. We have six companies in the group and each company has its own subgroups meaning each company must maintain its own databases with its subgroups.

Is there a way to simply copy the already worked out empty tables and Views in SQL server from an existing database to a brand-new database? Any short example will help.

Lastly, I want to use the VBA code below to relink the 120 tables on click event, for example I have about 120 tables in the current database, now the other remote users do not have Full MS Access, they are using MS Access Runtime which means that they will not have the inbuilt Link Manager to refresh the tables after installing of the ODBC either 32/64 BITs. Below is the code in question:

Private Sub Form_Open(Cancel As Integer)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.Connect = [ODBC] = "DRIVER=[SQL Server Native Client 11.0] " & _
"SERVER=Mwiinde\SQLExpress;DATABASE=Accounts;Trusted_Connection=Yes"
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing
Set dbs = Nothing
End Sub

The above code appears to corrupt my application unless there is something I'm missing in it. For example, the size of my application is 23mb, if I use the above code then it grows to 70 mb. That is why I think I'm missing something here.

Do you refer to copying the Linked Table objects (in Access), or do you refer to creating a new copy of the SQL Server objects?

If the former, copy & paste.

If the latter, right click, script as new ...