Runtime Access into SQL server 2000
-
Hi Guys,
This is my first post but I'm already enjoying this forum.
First of all, I am aware that this post has been covered many times before and I have read many of them.
I have an access 2000 database that I have upsized into SQL server 2000. I've changed the datatypes in SQL Server so they are more accurate and my application is working fine.
I have sucessfully generated a script from SQL that creates the tables in SQL server.
What I would like to do is install the access runtime onto another person's machine and then link the front end access to the backend SQL Server. Could someone please provide me with the steps that are neccessary to link the access runtime to the SQL server?
Thanks in advance
Woody
-
In access you can relink your linked tables dynamically using VBA.
http://www.mvps.org/access/tables/tbl0012.htm
You could read in the connection setting using a startup parameter, configuration file ...)
-
Hi Jo. Sorry to bother you again. I've put the code in a module, built the database but, I am unsure how to run it.
Could you tell me please?
-
Happy to help
The main relinking happens in fcRefreshlinks.
strNewConnect = the new link/connection to your tables.
findConnect(strDatabase, tdf.Name, strOldConnect)
is a function to get the updated connection.
see http://www.connectionstrings.com/ for possible connection strings
You could modify findConnect to look for connectionstrings in ini-files, commandprompt,...
-----------------------------
A small sample , quick and dirty
Create a shortcut to your database
"C:\Program Files\Microsoft Office\Office\Msaccess.exe" "C:\Program Files\Microsoft Office\Office\Voorbeelden\Noordenwind.mdb" /cmd "connect=pathtoupdatedconnectionstrings"
Make a macro called "Autoexec" (This macro always get executed unless the shift-key was been pressed during startup)
Let it start your "startup" procedure (moduleStartup.fcThingsToDoFirst)
public function fcThingsToDoFirst as integer
varUpdatedConnection ="" 'initialisation
if left(command,8)="connect=" then 'we've got new connections
'check if file exists
dim sPath as string
sPath=mid(9,len(command)) 'remainder of command args
'check if file exists 'can use Dir command
'open file for readonly
'read in the connections in a global module variable (varUpdatedConnection as string)
varUpdatedConnection = justreadfromfile
'close file
'perhaps some checks
fcRefreshLinks 'refresh linked tables
end if
end function
'modified
private function findConnect (strDatabase, tdf.Name, strOldConnect) as string
return varUpdatedConnection
end function
you can do the samething with the querydefs collection making some queries pass-through.
http://www.mvps.org/access/ was really helpfull for my access application
Also have a look at http://www.granite.ab.ca/Access/autofe.htm
a front end updater utility for Access
Feel free to ask more information
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply