Search results

you can use the sql command ALTER TABLE but I'm not sure if access supports it......?

Your server will have solid path and a virtual path name, the virtual path probably won't work forthis which is what i think is the problem your encountering. I'm assuming you on a windows network....? this is how it's set up on our network but should hopefully help you out open explorer and...

yeah, if you click the tables tab on your database manager thing in access then right click and link table. it'll walk you though the setting up of the dsn, it then saves the connection so you don't have to do it every time. I don't know how familiar you are with that sort of stuff, and it's...

hmmmm ok, lets go back a few steps. Is there reason your coding the conneciton yourself. Can you not just use the wizard in Access to set it up?

you mean SQL Server? I think problem is with access permisions on the SQL Server. if you give each user access rights to the (SQL Server) database with Windows Authentication then I think it will sort it out. chris

Do you mean the help files? they changed the way the help files work in Access 2000 < If you go into the build window and then functions hightlight StrComp and press f1. You should find the helps files. Same goes for any VBA help, you need to be in the code window.

err, it's been a while since I did that. I can't remeber which db it's in, but I'll try and remeber/work it out for you. OK you write the sp in SQL Server and the create a query in Access. Don't add any tables to the query. Select the menu 'Query' then 'SQL Specific' the 'Pass Trough Query'...

DoCmd.OutputTo acOutputQuery, [TableNameHere], acFormatXLS, ["C:AgentStats.xls"]

you need to code it into the VBA, I'm not sure if there is a menu option that will turn it off? If you right click the button that runs the query, build event then code. and put the line in after the sub and before any other code. don't forget to turn them back on again before end sub...

setwarnings false hth

Nice one cheers mate, finally got it, this is what I eneded up with. tableExists = Nz(DLookup("Name", "MSysObjects", "Name = 'LEAFile'"))

Thanks Richard, I can see where your going with this but I'm having trouble implementing it. this is what I have so far tableExists = DoCmd.RunSQL("SELECT MSysObjects.Name FROM MSysObjects WHERE ((MSysObjects.Name) = 'LEAFile')") If Len(tableExists) > 1 Then DoCmd.RunSQL ("DROP TABLE...

You can use this if EXISTS (select * from INFORMATION_SCHEMA.tables where table_name = 'Table8') for SQL server but not for access. Is there a way in VBA perhaps I could use. Thanks

doh! Call Shell("c:\windows\explorer.exe c:\windows\", vbNormalFocus)

This will open explorer Call Shell("c:\windows\explorer.exe\", vbNormalFocus) but I need to direct it to a specific file path. any ideas.......? thanks

Not sure if it'll work but try Set Warnings = False at the beggining of the code. don't forget to turn them back on again Set warnings = True

iI tihnk could also have just swapped a couple of lines around so it updates before the increment While Not rs.EOF If rs("Finish Miles") = rs("NextStartMiles") Then rs("TrackID") = TrackID Else rs("TrackID") = TrackID TrackID = TrackID + 1 End If Wend

So it increments the id for the last link of EVERY section of track, or just the very last one in the whole recordset?

Not sure if this will work in Access but if you go into the SQL of the query and just after "SELECT" type "TOP 1" To make sure you don't just get a ramdom Captain for each team you need to make sure that their ranked in some way.......

Finally got it to work Dim db As DAO.Database Dim rs As DAO.Recordset Dim TrackID As Integer Set db = CurrentDb() TrackID = 1 Set rs = db.OpenRecordset("qrytable1", dbOpenDynaset) While Not rs.EOF rs.Edit If rs("FinishMiles") =...