using sql server over a network

[REZ]

I have a split MS access database, I'd like to convert the backend to a SQL server on my server, and link it to frontends on other computers on the network.
I think i understand the process of exporting the tables from the MS backend to the SQL server, and I've managed to a frontend to the sQL server on the server, but how do I do it over a network?

 

[theDBguy]

Have you tried using the migration assistant (SSMA)?

 

[sonic8]

I think i understand the process of exporting the tables from the MS backend to the SQL server, and I've managed to a frontend to the sQL server on the server, but how do I do it over a network?

It should not make a difference whether the SQL Server is on the local machine or on the network.
You might need to configure the firewall on the server to allow external connections to the SQL Server though.

 

[REZ]

Do you have a code I could use to do it without a DSN?

 

[sonic8]

Not at the moment. - You should find examples when searching for "DSNless connection".

 

[REZ]

Thank you. I'll give it a try

 

[Josef P.]

DNS less connection strings: https://www.connectionstrings.com/sql-server/

 

[Galaxiom]

Pat's code is good way to convert connections.

However the easiest way to create DSNless connections on new tables is to first create a FileDSN. Use it to link the tables and Access will configure them with a DSNless connection.

 

[KitaYama]

However the easiest way to create DSNless connections on new tables is to first create a FileDSN. Use it to link the tables and Access will configure them with a DSNless connection.

I open a connection to server and add a recordset that contains the list of all tables. Then loop through the recordset and add dsnless linked tables to FE. It takes less than 5 seconds to add more than 150 linked tables and I don't need to take care of which tables are new. Nothing's done manually and all the admin needs to do is to click a button in Admin tab of the ribbon.

OpenConnection:
    stConnect = ....................
    With Con
        .ConnectionString = stConnect
        .ConnectionTimeout = 10
        .Open
        If .State = 0 Then Exit Sub
    End With
Return


OpenRecordset:
    sql = "select * from sys.tables where type = 'U'"
    rs.CursorLocation = adUseClient
    rs.Open sql, Con, adOpenStatic
Return