Use OpenDatabase to return a DAO.Database reference to your remote database. Then you can access a saved query via its QueryDefs collection. Here is an example from the Immediate window: set db = OpenDatabase("C:\share\Access\Database1.mdb") Debug.Print db.QueryDefs("Query1").SQL SELECT dbo_foo.bar, TypeName(bar) AS TypeOfBar FROM dbo_foo; db.QueryDefs("Query1").SQL = "SELECT d.bar, TypeName(d.bar) AS TypeOfBar"...

I have tested your code, I do not see any issues except for the fact, your For statement is a bit off and that you needed to set the db object. Try this code. Sub toto() Dim db As Database, i As Integer Set db = CurrentDb For i =...

Just to recap, without an ADO reference included in your project, you get a compile error at this line: If TypeOf adoRsColumns Is ADODB.Recordset Then Without the reference, VBA doesn't recognize ADODB.Recordset The situation is basically the same as if you tried to declare Dim rs As ADODB.Recordset without the...

Just a missing WHERE clause near the bottom: SELECT T.IU, 0.75*(SELECT Max(GM) FROM tblFirst250 WHERE tblFirst250.GM IN (SELECT TOP 25 PERCENT GM FROM tblFirst250 WHERE tblFirst250.IU = 1 AND GM Is Not Null ORDER BY GM)) + 0.25*(SELECT Min(GM) FROM tblFirst250 WHERE tblFirst250.GM IN (SELECT TOP 75 PERCENT GM FROM...

Dates in Access needs to be surrounded by the # tags so that it recognizes the date you have passed. The other important factor to consider is that JET requires the date format to be mm/dd/yyyy as opposed to the normal dd/mm/yyyy. So your problem is because you are using...

I managed to solve my issue. My code did not really change. cnxn = pyodbc.connect("DSN=BCTHEAT") cursor = cnxn.cursor() cursor.execute("select * from acr.Table_one_hh") row = cursor.fetchall() then I wrote the results into a csv file....

Access uses the # as a wildcard for a single number field. When you create a search field and include #, such as ART#, Access interprets this as "I'm searching for the string 'ART' followed by a single numeric character 0-9" It uses a '?','%', and '-' similarly. To get...

You could try an Excel table (they also have these functions in access if I recall, but I avoid access like a plague). To connect Excel to share point follow the steps in this article: support.office.com Ok, now that we are connected you should see all of the columns and...

The reason might because Passowrd is a reserved keyword in MS Access. You should use it with square brackets like [Password] update tbl_Clients set Username = @Username, [Password] = @Password where Id = @Id As a best practice, change it to non-reserved word. By the way, if you using OleDb...

DAO might be a little faster, but not materially. Instead, use an IN clause in your SQL Statement so you only have to do it once. Sub test() Dim vaIds As Variant Dim sSql As String vaIds = Split("1 2 4 7 200 205 654", Space(1)) sSql = "SELECT [Sales]...

Translate that form into Excel. Use "Collect Data" under the "External Data" tab on the ribbon to collect data from customers. To pull data onto your spreadsheet from Access do something like this. Use an Excel spreadsheet for the form. Then make a command button with the following event on...

Create a query which retrieves the QryName values from rows whose SubscriptionID matches the dropdown selection ... a query something like this: SELECT QryName FROM tbl_subcription WHERE SubscriptionID = [dropdown] ORDER BY QrySequence; Then you can open a DAO.Recordset based on that query, move through the recordset rows, and execute...

You should change your code to something like the following. Note that Everything that returns an object like OleDbConnection, OleDbCommand, or OleDbDataReader is wrapped in a Using block. These objects all implement the IDisposable interface, which means they should be cleaned up as soon as you're done with them. Also...

I asked a similar question on another forum and got this answer: http://www.accessforums.net/queries/sql-aggregate-25-percentile-value-subsets-ms-53125.html#post280306 ...which is correct. Thank you for all your help Mark C. Hopefully this saves you some time. Here is the guts of it, but not all of it. Basically a loop through GICS Sectors: Option Compare Database...

The problem is, that the time is in the SQL server stored as datetime. So the field in the linked table is a datetime. When the time is stored as a time(7) in the SQL server, the field in the linked table will get a text. And then the select...

Your query is correct but probably you have zero length strings in your Contact column. You can use select * from table where len(Nz(Contact, '')) > 0 The Nz function returns the specified default value if the column is null....

GROUP BY works by placing rows into groups where values are the same. So, when you run your query on your data and it groups by field1 and name, you are saying "Put these records into groups where they share a common field1 and name value". If you want 4567890,...

The following query will produce an error in Access, because it's trying to compare two field names: "EmployeeName" and "kevin": Select * From reportForm Where EmployeeName = kevin The following query should not produce an error, because it's comparing "EmployeeName" against a string value of "kevin": Select * From reportForm...

I received an error from the query below stating something along the lines of "this query can only return a maximum of one row". But the query along with this reference: Calculating time difference between activity timestamps in a query gave me what I needed which I am listing below...

Look in the VBProjects collection and check each project's FileName property. If a project's FileName is the current database file (CurrentDb.Name), that is the one you want. Public Function ThisProject() As String Dim objVBProject As Object Dim strReturn As String For Each objVBProject In Application.VBE.VBProjects If objVBProject.FileName = CurrentDb.Name Then...

Whenever I have to calculate a field and use the results in a second field, I use a common table expression to make the first calculation. Given this query, it could look like this: with cte_preprocess as ( SELECT tbl_ShiftSched.Shift, tbl_ShiftSched.SortInd, [ShiftStart]+ case when @dteFTm>[shiftstart] then DateAdd(day,1,@dteShf) else @dteShf end...

your query is wrong SQL SERVER the syntax for datediff is DATEDIFF(datepart,startdate,enddate) Also the function for getting current date in sql server is getdate() not now() so in your case it will be Select DATEDIFF(DAY,reservations.checkin_date, getdate()) eg:- select DATEDIFF(Day,'06-07-2015 14:00:00',GETDATE()) will return 10 MS-ACCESS DateDiff ( interval, date1, date2, [firstdayofweek],...

It looks like you bulk-inserted the raw binary image data directly from the BMP file into the VARBINARY column using T-SQL. Therefore, those images don't have the "OLE wrapper" that is added when an image is inserted via a Bound Object Frame on an Access form. See the my answer...

Don't use Set when you assign a value to a simple variable type. 'Set badqueryname = "qry_InformationMailer" badqueryname = "qry_InformationMailer" Or, if you wish, you could use Let instead of Set. Let badqueryname = "qry_InformationMailer" But I don't see Let used very often anymore. Actually, in your code the value...

You do have a complicated mess with those calculated fields. Why not join more directly? This query below leaves one '/' unaccounted for, but should tell you what I'm thinking of. SELECT t1.sb, left(st1.uchbegriff,7) & val(right(t1.suchbegriff,4)) AS suchbegriff2, t1.menge FROM kvks AS t1 INNER JOIN konf AS t2 WHERE (t1.suchbegriff...

There a number of other problems with the code (sql injection, sharing a connection among several commands), but here's a step in the right direction: Try conn.Open() cmdfoods.ExecuteNonQuery() cmdservices.ExecuteNonQuery() cmdreservations.ExecuteNonQuery() bill.ExecuteNonQuery() success = True Catch success = False Finally conn.Close() End Try A more-complete solution: Private Function save_to_data() Dim sql...

Assuming WHERE columnc = 20 selects 1000+ rows, as you mentioned in a comment, executing that UPDATE statement should be noticeably faster than looping through a recordset and updating its rows one at a time. The latter strategy is a RBAR (Row By Agonizing Row) approach. The first strategy, executing...

Try this. This will exclude the [DSRT_ERS].[ID] from DSRT_TEMP table and insert only filtered records. INSERT INTO DSRT_ERS SELECT * FROM DSRT_TEMP WHERE [DSRT_TEMP].[ID] NOT IN (Select [DSRT_ERS].[ID] FROM [DSRT_ERS] WHERE [DSRT_ERS].[ID] IS NOT NULL) There are two options, you have NOT IN and NOT EXISTS suggested by @deoeth. Keep...

DateSerial should make this easier. Give it values for year, month, and day in that order, and it will give you back the corresponding date as a Date/Time value. for dt = DateSerial(Year(Date), 1, 1) to DateSerial(rs!dan, 2, 2) msgbox dt Next ...

Your problem is in the line: Public Property Get ignoreDifferencesInDatabaseComparisonForFields() As String() ignoreDifferencesInDatabaseComparisonForFields = pIgnoreDifferencesInDatabaseComparisonForFields ' ^ correct typo here ^ End Property When I fixed this, your code compiled, and it ran properly without the Subscript out of range error....

Following our discussion in Comments, I have updated your complete code to what it should be. Again, I don't have an Access database handy to test it but it compiles and should work: Sub vardaily() Dim db As Database Dim rs As DAO.Recordset, i As Integer, strsql As String Dim...

Based on what I have read so far, I think I can offer some suggestions: It appears you have control of the MS Access. I would suggest adding a field to your data table called "source". Modify your form in the access database to store something like "m" for manual...

You can use COM automation from VBScript to create an Access application instance, hide it from view, run your macro, and then shut down Access. I saved the following script as RunAccessMacro.vbs and ran it in a command prompt window using this as the command line ... cscript //NoLogo C:\share\vbscript\RunAccessMacro.vbs...