Access forms to SQL Database

Question

Access forms to SQL Database

daversb

SSC Journeyman

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

#175363

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 7 posts - 1 through 6 (of 6 total)

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

I Charters SSC Enthusiast Points: 117 More actions · Answer 1

I think you need to create a new Access Project (Existing data). The creation process includes linking to a SQL database.

Once that is done you will have all your tables available and then all you need to do is import everything else from your previous front end

Randy Helms SSC Enthusiast Points: 139 More actions · Answer 2

Create a file DSN to link to your SQL Server db. Use the DSN to link to your sql tables, as long as they are named the same as your access tables your front end won't know the difference.

daversb SSC Journeyman Points: 85 More actions · Answer 3

right, but the problem is that the dbo_ is screwing everything up.....

Dave

I Charters SSC Enthusiast Points: 117 More actions · Answer 4

Just had another look at my access project and I don't see the dbo_ bit at all. Don't use an Access database (where the dbo_ occurs) - use an access project and avoid it

Randy Helms SSC Enthusiast Points: 139 More actions · Answer 5

Public Function Remove_Dbo_()

For Each tdf In CurrentDb.TableDefs

Do While left(tdf.NAME, 4) = "dbo_"

tdf.NAME = Mid(tdf.NAME, 5)

Loop

Next tdf

End Function

daversb SSC Journeyman Points: 85 More actions · Answer 6

Was able to get the tables to link with the help of Bob B.

Refrence the following Post:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=131&messageid=322662

Thanks,

Dave