Linking Access Forms to SQL 2005 Express

Question

Linking Access Forms to SQL 2005 Express

daversb

SSC Journeyman

Points: 85
More actions
November 15, 2006 at 12:54 pm

#67809

I currently am in the process of moving my companies database from access to sql 2005 Express. I am testing on a 64bit xp machine and the hosting of sql is fast and working great. upsize of access worked fine with only a couple of minor errors that i have to manually build those tables (no big deal). Everything on the sql is working fine.

Here is the issue (finally); obviously my whole MDE. front end of my company database has been built to link forms, queries, and reports to my microsoft access database. I quickly made two forms in design wizard, and was able to link everything properly and access all of my records in sql.
If anyone has a solution as to how i can prevent myself from having to design all new forms, queries, and reports that would be wonderfull. I have well rounded sql experience but linking access to sql is a different type of animal.

Any help would be much appriciated.

Thanks,
Dave

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Richard O'Sullivan Ten Centuries Points: 1118 More actions · Answer 1

Dave,

Set up an ODBC DSN to point to your Database. In Access use File - Get External Data - Link Tables and point to your ODBC - DSN - the tables in your database will be listed for you to select.

Linked files will have a dbo_ prefix. Use the function below to remove the prefix and they should operate like a linked Access table. The Refresh ODBC is for when changes are made on SQL and access needs the latest updates.

HTH

Richie

Function RefreshODBC() As Collection

Dim tdf As TableDef, db As Database

Set db = CurrentDb

db.TableDefs.Refresh

For Each tdf In db.TableDefs

With tdf

If Len(.Connect) > 0 Then

If Left$(.Connect, 4) = "ODBC" Then

' Debug.Print tdf.Connect

' tbl.Connect = Replace(tbl.Connect, "RTSERVER", "Ross28\ross")

tdf.RefreshLink

End If

End With

Set db = Nothing

MsgBox "Refresh Complete"

End Function

Sub RemoveDBO()

Dim tbl As TableDef

For Each tbl In CurrentDb.TableDefs

If Len(tbl.Connect) > 0 Then

tbl.NAME = Replace(tbl.NAME, "dbo_", "")

End If

MsgBox "Remove Complete"

End Sub

daversb SSC Journeyman Points: 85 More actions · Answer 2

It ended up not working and came up with a debug error, of 3003?

also, can you clarify the need to edit areas of the funct command....

Thanks,

Dave

daversb SSC Journeyman Points: 85 More actions · Answer 3

Sorry, to clarify when i try and run the second part....in the Sub command, that is when the debug error comes up. The first Function works fine on refreshing the tables.

Thanks,

Dave

Richard O'Sullivan Ten Centuries Points: 1118 More actions · Answer 4

Dave,

You may have to set a reference to DAO 3.6 Object model.

Richie

Bob Baldwin Valued Member Points: 53 More actions · Answer 5

You might like to look at the answer I put in a previous question this week at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=131&messageid=322662.

This code 1. removes all links, 2. asks for the DSN that you want to link to, and 3. Attaches all tables in the SQL database you pointed to.