December 20, 2006 at 12:22 pm
I am setting up an Access front end to a SQL server database and would like to know how to attach the SQL tables to the Access database so I can distribute the Access database to 50 to 100 separate PCs.
I know how to link tables to Access, (File/Get External Data/Link Tables.../"Files of type:" = ODBC Databases() ) but the problem is how to do it so I can distribute the Access database.
Thanks,
December 20, 2006 at 1:15 pm
Is it an Access database or a SQL Server database? Your information says it's an Access FRONT-END and a SQL Server database. An Access front-end is the forms that are used to access the data in the SQL Server db.
So what exactly are you really trying to do? Import the data into an Access database?
-SQLBill
December 20, 2006 at 1:51 pm
I am LINKING the SQL Server tables to an Access database. I used the word Attach, sorry. SQL Server will be the back-end (data store) for the Access front-end.
December 20, 2006 at 2:28 pm
Hi Christopher
I take it that you don't want to go around creating a DSN entry in every workstation that you deploy the database to.
Here is quick snippet for how to link tables dynamically without using a DSN entry.
Dim dbLocal As DAO.Database
Dim tdfAccess As DAO.TableDef
Dim dbSql As DAO.Database
Dim strConnect As String
strConnect = "Driver={SQL Server};Server=ServerName;Database=DatabaseName;Trusted_Connection=Yes"
Set dbLocal = CurrentDb
Set dbSql = OpenDatabase("", False, False, strConnect)
Set tdfAccess = dbLocal.CreateTableDef("tblUserReport")
tdfAccess.Connect = dbSql.Connect
tdfAccess.SourceTableName = dbSql.TableDefs("dbo.tblUserReport").Name
dbLocal.TableDefs.Append tdfAccess
Set dbSql = Nothing
Set dbLocal = Nothing
Also take a look at this article
http://www.accessmvp.com/djsteele/DSNLessLinks.html
Roberto
December 21, 2006 at 9:49 am
What version of Access are you using? If its 2000 or greater, why not just create an Access project and then you dont have to worry about linking tables to SQL. I recently converted all our .MDB's to projects with SQL back end and it is much easier to maintain.
David
December 21, 2006 at 11:59 am
Roberto,
Thanks! that is exactly what I was looking for. Now, if I can only get it to work.
Everyone else,
Thanks for your help! It is always nice to know there are people that have been there before that are willing to share.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply