Unable to access Access form after migration to SQ

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Unable to access Access form after migration to SQ

Author

Topic

jerill
Starting Member

7 Posts

Posted - 2011-10-25 : 08:28:06

Hi,

I have migrated Access 2003 to SQL Server 2008 R2 using Upsizing Wizard and Linked Access Tables to SQL Server 2008 R2, so that I can still use Access Forms ( Reports ).

I am fetch data from Access form on server. But I go to client machine and try to access, Access Form, I get following error message :

Connection failed:

SQLState: '28000'

SQL Server Error: 18456

[Microsoft][ODBC SQL Server Driver][SQL Server]Login Failed for user servername\guest failed.

Any idea ?. I am not using "Guest" user but I don't know how that is coming into role.

Thanks

G37Sam
Starting Member

16 Posts

Posted - 2011-10-25 : 09:42:18

Are you using Windows Authentication or SQL Authentication?

If SQL Authentication, you need to specify a username and password

Sam

jerill
Starting Member

7 Posts

Posted - 2011-10-25 : 09:48:23

I have set authentication method as Mixed mode in SQL Server 2008 R2.

I am entering "sa" as username and password while connecting to SQL management studio.

G37Sam
Starting Member

16 Posts

Posted - 2011-10-25 : 10:03:30

If you are able to access the server from SQL Management Studio it means the problem is within Access itself

My guess is its in the connection string

Are you connecting through DSN or without it?

Sam

jerill
Starting Member

7 Posts

Posted - 2011-10-25 : 10:05:43

Hi,

I am not using any DSN. I am not sure whether I should be using that or not ? Which DSN should I be using ? On client or Server ?

G37Sam
Starting Member

16 Posts

Posted - 2011-10-25 : 15:59:13

Ok, write the following code in a new module and run it

Sub ADOCnxn()

Dim cnxn As New ADODB.Connection
Dim rs As ADODB.Recordset

cnxn.Open "Provider=sqloledb;" & _
            "Server= serverNameHere ;" & _
            "Database= databaseNameHere ;" & _
            "Uid=sa;" & _
            "Pwd=password"

Set rs = New ADODB.Recordset
rs.Open "tableNameHere", cnxn

msgbox rs.fields(0).value

rs.Close
cnxn.Close

End Sub

What do you get?

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources