Booting Remote Users

[Lightwave]

Congratulations on getting that working. Sorry don't have direct experience of this however from a quick search on the web it is yet another problem that users regularly experience. I found this thread which looks promising.

http://www.tek-tips.com/viewthread.cfm?qid=1240616

see gmmastros - Importantly he says

Each SQL Server has a default language. You can see what the default language is by executing these commands (in Query Analyzer).

sp_configure 'default language'

And he goes on to give the exact command line to change it presumably in Query Analyzer. I haven't used Query Analyzer before so could be wrong.

 

[SmallTime]

Hi Lightwave,

I've also been searching the web for an age and came up with a big zilch. It seems a lot of people are having the same issues. In a nutshell what I found was that SQL Server wont allow any other date convention other than American. I've also noticed a few people using code that changes date to a string which I think is the worst possible workaround.

I've put the above on the back-burner at the moment as I need (or feel) I should get to grips with T-SQL, or rather pass-through queries so I can get the processing workload shifted from my front-end to server side. Most if not all my forms use queries with multiple parameters which I believe will have to made built on the fly.

Last few days have been quite a learning curve for me, feels a bit like I jumped in at the deep end of a mud pool, It seem every step of converting to SQL Server produces it's own unique challenges.

for example, pass through queries need a connection string, which have to be changed for every query depending on dev. or deployment environment.

In case anyone else follows this post here's what I did (for Windows Authentication)
Thanks in no small part to an old post from Doug Steele

In a command burton on my admins form

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim srvConStr As String

If SomeConditionToShowDeployedMode then
'Connection string for Deployed Mode
srvConStr = "ODBC;DRIVER=SQL Server;SERVER=YourDeployedServerName;Trusted_Connection=Yes;DATABASE=YourSQLDatabaseName"
else
'Connection string for Deployed Mode
srvConStr = "ODBC;DRIVER=SQL Server;SERVER=YourDevServerName;Trusted_Connection=Yes;DATABASE=YourSQLDatabaseName"
end if

Set dbCurr = CurrentDb()
For Each qdfCurr In dbCurr.QueryDefs
If Len(qdfCurr.Connect) > 0 Then
qdfCurr.Connect = srvConStr
End If
Next qdfCurr

Set dbCurr = Nothing



Next, I think I'm going to brave it out a little more and see if I can't learn how to make a query in my SQL DB (I think the term is View but not sure) and call it from Access, might save a lot of tinkering around with querydefs if I can get it accept parameters.


Thanks for you kindness and time
SmallTime

 

[SmallTime]

For the benefit of others that might stumble on this thread

re the date format problem with SQL Server mentioned above.

I explicitly formatted a DateTime field in one of my queries that's based on a DSN less linked table e.g.

DOpened: FormatDateTime([clsOpened],2)

After running the query all of my date formats have now returned back to dd\mm\yyyy from yyyy\mm\dd, not just ones that rely on that query but throughout my front-end.Can't explain it, but that's what happened. How Odd

SmallTime