Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    May 2006
    Posts
    65

    Unanswered: Connection Failed - why? my strings dont use trusted connections

    Hi,

    Ive set up a database which is a fe and be, with the tables residing on a sql server. The database is based on its own tables but also has some views which references another database.

    The problem i have is that when logged in as one of my users one of my queries doesn't work, i can access all tables and forms (which have embedded queries in them) but when i try and access a particular query i get this error message:

    Connection Failed
    SQL State: '28000'
    Sql Server Error: 18456
    [Microsoft][odbc sql server driver][sql server]Login Failed for user 'Valhalla\a.marshall'

    None of my connection strings use trusted connections- all use a generic sql logon and i can access all of the tables except a listbox which is based on query. so am not sure what is happening.

    The query in question is a union query, one that relies on a selection from a dropdown which then fires a vb statement to amend the query in the record source property box:

    Private Sub StuName_AfterUpdate()

    StuID = StuName.Column(1)
    FormCombi = StuName.Column(2)
    On Error GoTo takedefault:
    Me.Photo.Picture = "\\Sqlserver\PupilPhotos\" & StuName.Column(1) & ".jpg"


    StuMail = "SELECT dbo_qxStudents.StudentId, [Form] & '@pgs.org.uk' AS Email FROM dbo_qxStudents where dbo_qxStudents.StudentId = " & Me.StuID.Value & "; Union SELECT dbo_qxStudents.StudentId, LCase([key stage]) & '@pgs.org.uk' AS kEmail FROM dbo_qxStudents INNER JOIN Emails ON dbo_qxStudents.Form = Emails.Class where dbo_qxStudents.StudentId = " & Me.StuID.Value & "; Union SELECT dbo_qxStudents.StudentId, Replace('year' & Left([FORM],1),'yearr','year0') & '@pgs.org.uk' AS [Year]FROM dbo_qxStudents where dbo_qxStudents.StudentId = " & Me.StuID.Value & ""

    Me!elist.RowSourceType = "table/query"
    Me!elist.RowSource = StuMail

    Exit Sub
    takedefault:
    Me.Photo.Picture = "\\Sqlserver\PupilPhotos\0000.jpg"

    so if i copy the code and run it as query it works, if i run it as vb it for some reason tries to connect to a table as another user and doesn't use the default logon set up in the connection string. help!!!

    any ideas would be gretly appreciated.

    greg

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi dubs

    Interesting domain name

    How about knocking those semi colons out - does this help?
    Code:
    StuMail = "SELECT dbo_qxStudents.StudentId, [Form] & '@pgs.org.uk' AS Email 
    FROM dbo_qxStudents 
    where dbo_qxStudents.StudentId = " & Me.StuID.Value & " 
    Union SELECT dbo_qxStudents.StudentId, LCase([key stage]) & '@pgs.org.uk' AS kEmail 
    FROM dbo_qxStudents INNER JOIN Emails ON dbo_qxStudents.Form = Emails.Class 
    where dbo_qxStudents.StudentId = " & Me.StuID.Value & " 
    Union SELECT dbo_qxStudents.StudentId, Replace('year' & Left([FORM],1),'yearr','year0') & '@pgs.org.uk' AS [Year]
    FROM dbo_qxStudents 
    where dbo_qxStudents.StudentId = " & Me.StuID.Value & ""
    Presumably if you go to the db window and double click:
    dbo_qxStudents
    emails

    they both open fine?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2006
    Posts
    65
    haha, yes we have had odin and draugr- must be bored over in IT....

    Yes the query works when ran as a query, ive tried knocking out the semicolons and just have to log on as the other user to check that- the code still worked...

    ill be back

    greg

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    makes an interesting change from the Star Trek obsessed sites I seem to come accross all to often......
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2006
    Posts
    65
    Hi,

    that hasn't worked. it's almost as if as soon as i try and run the query through vb that it tries to force a trusted connection as that's what the connection dialog has defaulted to after i click ok on the error message.

    otherwise viewing the code through a query, or even the raw form code (ie contents of list box before id selection is made and fires the vb) is fine meaning i dont think its a server permissions issue.

    the two databases have the same users on and it allows me to access the views stored in the main database but which reference the secondary database, so i dont think its anything to do with this either

    annoying thing is this was all fine when i wrote it- its only now ive rolled it out that it cant actually be used.....

    greg

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you checked the permissions on the table(s) and or query to make sure that the specified user has valid permissions.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2006
    Posts
    65
    Hi,

    yes, the queries run fine when accessed via anything else than the vb code.


    ive tried adding this line

    Dim objCurrent As Object

    Set objCurrent = Application.CurrentProject
    MsgBox "The current base connection is " & objCurrent.BaseConnectionString


    to the vb code to see what's happening to the connection string and it comes up as this:

    The Current base connection is PROVIDER= microsoft.Jet.OLEDB.4.0;DATASOURCE=\\sqlserver\adm in\js\Junior School Pastoral LogNew.mdb; PERSIST SECURITY INFO= FALSE; Jet OLEDB: System Database = c:\documents and settings\g.spicer\application data\microsoft\access\system.mdw

  8. #8
    Join Date
    May 2006
    Posts
    65
    this is weird because its pertaining to the fe version of the db not the sql tables and also it sounds like there is a security file causing a problem but i have no security set on the db....

    greg

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Forgive me if I am making you repeat yourself but if you pop in:
    Code:
    Debug.Print StuMail
    Could you -
    post the result?
    Paste it into a query and run?
    Create a new form and paste it into the recordsource and open?

    Also:
    Quote Originally Posted by dubs
    the two databases have the same users on and it allows me to access the views stored in the main database but which reference the secondary database, so i dont think its anything to do with this either
    Do you mean you have linked SQL Servers or are using a heterogenous query (e.g. with Openrowset)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    May 2006
    Posts
    65
    Hi,

    i didnt know exactly where to put that debug line of code but wherever i put it it didnt seem to affect anything, the vb still ran as normal- requiring the login details to be changed before the query could run....

    it's linked sql server, basically some views which reference tables in a different dataset- to give some context the main db is a collection of tables for recording comments and the db it references is our student db from which i drag id, name, year and class data into a view (one thats filtered to certain year groups). the query in the vb references that view, it's fine when ran as a query, or when displayed as list box data on the form but as soon as you run the vb code to alter the select statement to include the value of the dropdown i get the error...

    greg

  11. #11
    Join Date
    May 2006
    Posts
    65
    I tried again (using my brain this time) and got this in the immediate window:

    SELECT dbo_qxStudents.StudentId, form & '@pgs.org.uk' as email FROM dbo_qxStudents where dbo_qxStudents.StudentID = 3944 Union SELECT dbo_qxStudents.StudentId, LCase([key stage]) & '@pgs.org.uk' AS kEmail FROM dbo_qxStudents INNER JOIN Emails ON dbo_qxStudents.Form = Emails.Class where dbo_qxStudents.StudentID = 3944 Union SELECT dbo_qxStudents.StudentId, Replace('year' & Left([FORM],1),'yearr','year0') & '@pgs.org.uk' AS [Year]FROM dbo_qxStudents where dbo_qxStudents.StudentID = 3944

    this works, when i try and do it as another user the code fails before it gets to the debug line, in fact looking at the contents of the StuMail shows it as empty, so im guessing the code hasn't got to the point where it assembles the string,

    this indicates that the code is failing before its asked to run any queries or fails in the query itself returning a value of 'empty' as StuMail

    greg

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Have a quick read about debugging, breakpoints and stepping through code in help (I advise you take a longer period of time reading up and practicing in the future when you have more time)

    Set a breakpoint at this line:
    Code:
    StuID = StuName.Column(1)
    Setp through the code and let us know which is the line immediately before the failure (i.e. the line which causes the error when it executes).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    May 2006
    Posts
    65
    Hi,

    thanks for the help- as is probvably obvious i have no formal training which explains the gaps in my knowledge.

    the code fails after this line:
    Me!elist.RowSource = StuMail

    everything runs fine upto that poiint and then the error appears- annoying thing is the vb value tag isn't big enough to show the whole value of my string it just shows the first 100 characters or so.

    i will look into this in more depth, but from my limited knowledge im stumped.

    greg

  14. #14
    Join Date
    Jul 2005
    Posts
    50
    Quote Originally Posted by dubs
    Hi,
    <snip> - annoying thing is the vb value tag isn't big enough to show the whole value of my string it just shows the first 100 characters or so.
    </snip>
    greg
    Type the string name into the immediate window, with a "?" immediately before it, press return, and you will get your whole value displayed.

  15. #15
    Join Date
    May 2006
    Posts
    65
    could it be that the string is too long? looking at the watch window for stumail shows that the value is truncated here:

    "SELECT dbo_qxStudents.StudentId, Form & '@pgs.org.uk' AS Email FROM dbo_qxStudents where dbo_qxStudents.StudentID = 1943 Union SELECT dbo_qxStudents.StudentId, LCase([key stage]) & '@pgs.org.uk' AS Email FROM dbo_qxStudents INNER JOIN Emails ON dbo_qS"

    firstly there isnt a view called dbo_qS it is dbo_qxStudents and secondly i counted the characters in word and the total is suspiciously near the 255 characters you are allowed for a string in an sql db....coming in at 258

    greg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •