May 15, 2014 at 2:52 pm
jdasilva (5/15/2014)
Sean Lange (5/15/2014)
jdasilva (5/15/2014)
ok. well lets go back to the original stuff Sean posted. He used 2 delcarations that don't work (user-defined type not defined):
dim parm as ADODB.Parameterdim cmd as ADOBD.Command
So here is my first issue. These 2 items seem to be key in the code, but if I can't declare them, I can go no further. I have tried with and without New in the declaration.
If you are doing this in Access you may have to add a reference in the project. It has been a LONG time since did that in Access. From your code window go to Tools -> References. Then iirc it is something like "Microsoft ActiveX DataObjects" or something like that.
This is starting to get more complex... I am not the only one using the front end, which would mean I would have to turn this on for everyone at this time, and in the future that will be working with this... I am taking a step in the wrong direction now. I think I should go back then to the DAO method...
Access is only a single file. Are you saying you have lots of copies of this around and each person has their own copy?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2014 at 9:18 pm
Even if they do, so what? Just push a new front end out. Or is each user modifying his own front end? In that case, you might need some kind of version control. You might want to check out Tony Toews' AutoFE Updater. http://www.granite.ab.ca (if I remember right).
May 15, 2014 at 11:30 pm
jdasilva (5/15/2014)
This is starting to get more complex... I am not the only one using the front end, which would mean I would have to turn this on for everyone at this time, and in the future that will be working with this... I am taking a step in the wrong direction now. I think I should go back then to the DAO method...
That's one of the reasons why I prefer using the DAO library when possible. It's the native Data Access Library used by MS-Access. All "data-aware" objects in Access (linked tables, queries, bound forms, bound reports, combo boxes, List boxes, etc.) use DAO.
Another reason is that DAO is usually a tad faster than ADODB.
Another is that there are more features available in DAO not present in ADODB than the contrary, although some DDL properties when defining a table are only available in ADODB (see: http://msdn.microsoft.com/en-us/library/office/aa164825(v=office.10).aspx or http://www.utteraccess.com/wiki/index.php/Choosing_between_DAO_and_ADO).
For several years MS tried to convince Access users of replacing DAO with ADODB, while it never stopped using DAO internally. This nonsense has ceased now and the default reference has returned to being DAO. If you want to use ADODB you must explicitly add it to the references of your project.
Have a nice day!
May 20, 2014 at 6:57 am
It is a front end that is duplicated across, but the changes being made are not to access, but to the access installation. Meaning if the staff managing the database change, then we need to be sure to update the access installation. I'm really the only one involved that would know enough to do that. Most of the staff using the database are not that computer savvy. Which is why I have to create a "push one button" front end for them.
May 20, 2014 at 8:14 am
Then this should probably do it:
May 21, 2014 at 10:20 am
Updating the file is not too bad an issue. I could create my own batch file to do that. They all exist in the same place on each local server.
Ok, looks like I got it to work. Here is my code:
Private Sub Box42_Click()
'name of the query for the pass through query
Const c_Name As String = "newPhoneListQuery"
'name and parameters of the Stored procedure
Const c_SQL As String = "GetThisLocCode @P;"
'connection to SQL server
Const c_Connect As String = "ODBC;Driver={SQL Server};Server=Ontario;Database=CNRFloorPlan;TrustedConnection=Yes"
Dim qdf As DAO.QueryDef
'create the query if it does not exist
If DCount("*", "MSysObjects", "name = '" & c_Name & "'") = 0 Then
Set qdf = CurrentDb.CreateQueryDef("")
With qdf
.Connect = c_Connect
.Name = c_Name
.SQL = c_SQL
End With
End If
Set qdf = CurrentDb.QueryDefs(c_Name)
With qdf
'reconnect just in case
.Connect = c_Connect
.SQL = Replace(c_SQL, "@P", Me.ThisLocCode.Value)
End With
Set qdf = Nothing
End Sub
a little different that on the first page of this thread, but was enough to get me going. In my searches I also found that this all could be done with just 2 lines by creating the query in access itself.
The jist of it is here:
(needed to know the name 'pass though query')
the above code sets up the ODBC connection and the SQL string that is the query. But it is good to know how to code it as well, incase I move away from Access (our company is shifting to Oracle, so not sure how things will be a year or so down the line).
Thanks all for the help in understanding this and all that surrounds it.
Viewing 6 posts - 46 through 50 (of 50 total)
You must be logged in to reply to this topic. Login to reply