1

I have an access 2010 application with a SQL Server database.

But I need to do an offline version. So I thought I would create a local SQL Server database on their computers then they can run a script to update their data before they go on the road.

NOTE: There won't be any sync. The data in offline mode is only for read-only and any changes will be lost.

I tried with Management Studio like this:

enter image description here

But I realized that the data is hard coded instead of doing inserts from selects.

Is there any easy way to create the script?

What I have so far is my pass through query in access to create the backup of my online database. Then I have my pass through query to restore the backup to the local server.

The only problem is how can I build the connection string for the second query. It's currently set to this one

ODBC;DRIVER=SQL Server;SERVER=010-068\SQLEXPRESS;UID=marcAndreL;Trusted_Connection=Yes;DATABASE=SMD

but because it's a different database for everyone, it won't work.

How can we build a custom connection string?

Community
  • 1
  • 1
Marc
  • 16,170
  • 20
  • 76
  • 119
  • Is creating a back-up file and restoring it in your local machine an option? The opposite of this http://stackoverflow.com/questions/4723195/recreate-local-copy-of-sql-server-database-for-server or this http://stackoverflow.com/questions/243583/easiest-way-to-copy-an-entire-sql-server-database-from-a-server-to-local-sql-exp – Fionnuala Dec 17 '12 at 17:42
  • Should I do a pass through query to create backup file then another one with the local connection string to restore the backup? Would it work? I got that script already http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/ – Marc Dec 17 '12 at 19:03
  • I have not tried it, so I cannot be sure, but it sounds good. You are more in the area of SQL Server than MS Access on this one, and it may be worth asking with suitable SQL Server tags. – Fionnuala Dec 17 '12 at 20:02
  • I updated my question. Is there an easy way or I have to build one in VBA? – Marc Dec 17 '12 at 20:52
  • You want a custom connection string for each user for a passthrough query? If so, the easiest way is file dns. I will post some notes. – Fionnuala Dec 17 '12 at 21:04
  • I got a file DNS for mine but I will have to create a different DNS for everyone? – Marc Dec 17 '12 at 21:08
  • Yes, but you will not have to change the query each time and they are just text files. If you prefer you can change the connect property: `currentdb.QueryDefs("MyPassthrough").Connect="ODBC;connect string here;"` But this will mean heeping a record of who is to have which string. – Fionnuala Dec 17 '12 at 21:15
  • is there a way with localhost and the windows authentication? So that would be the same string for everyone – Marc Dec 18 '12 at 14:04
  • Yes, there is http://stackoverflow.com/a/5283762/2548 works fine for me. – Fionnuala Dec 18 '12 at 14:19
  • That's what i'm trying but it gives me a invalid connection string. Can you write this as an answer plz – Marc Dec 18 '12 at 14:27

2 Answers2

1

I am using SQL Server Express 2012 and Windows Authentication, so using the answer provided here, I find this works for me:

Sub TestCon()
Dim cn As New ADODB.Connection

    cn.Open ServerConLocal
End Sub

Function ServerConLocal()
    ''OleDB Connection
    ServerConLocal = "Provider=sqloledb;Data Source=localhost\SQLEXPRESS;" _
    & "Initial Catalog=Test;Integrated Security=SSPI;"
End Function

For an ODBC connection string in a Pass-through query, this works for me:

ODBC;Driver={SQL Server Native Client 11.0};Server=localhost\SQLEXPRESS;Database=test;

Trusted_Connection=yes;

Community
  • 1
  • 1
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
0

Take a look at download-only articles for merge replication. MSDN.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68