I'm trying to INSERT INTO without duplicates in MS Access from VB.NET. But I get an error: missing semicolon (;) at end of SQL statement Is there something wrong with the SQL code? Please guide me Thanks INSERT INTO datfiles (ID, [DATE], [TIME], FP, INOUT, OTHERS1, OTHERS2) VALUES (1000, '25/04/2024', '15:00', '1', 'IN', '1', '1') SELECT t1.ID, t1.DATE, t1.TIME FROM datfiles t1 WHERE NOT EXISTS (SELECT ID, DATE, TIME FROM datfiles t2 WHERE t2.ID = t1.ID AND t2.DATE = t1.DATE AND t2.TIME = t1.TIME ..read more

This is a follow-up question on A working RegEx match that begins at the first of two OR-words takes the unwanted last OR-word instead if I place more RegEx before it [duplicate]. Thanks go to the helpful remarks of this user and this user under that question. I got: (?>=SELECT\s+|,\s*)(.+)\s+AS\s+Error.?(?<=WHERE|HAVING)(.) Or without any lookarounds: (?:=SELECT\s+|,\s*)(.+?)\s+AS\s+Error.?((?:WHERE|HAVING).) as the needed RegEx for: SELECT a, bc d AS Error FROM y WHERE 1=1 HAVING 1=1 to be split into two matching groups: bc d as the code that builds the column called "Error". T ..read more

I'm trying to format a figure in an access form to read in millions but retain the 0. in how it displays. Using the formatting: #,###,,.0 makes my figure of 200,000 read as .2 which is great.... But I need it to show as 0.2 The data coming from the query is definitely fine as if I remove the formatting altogether and set the value as 0 it displays 0.00 I've also tried making small adjustments to the format but with no luck. Is there a tweak to the format above or a revised one which will set me straight? Many thanks ..read more

Background I have some software for a piece of analytical instrumentation (UV WinLab) that uses a .mdb (Access2000) database. The software can only use this specific type of database and all data operations must take place in the original software-configured tables. I would like to link this Access database to a 'mirrored' copy that runs on SQL Server (Express 2014), so that I can provide other systems access to this data. Notably, the UV WinLab software can only write to/modify the original Access database/tables, but I still need a 'mirrored' SQL version of the database to expose the data to ..read more

I'm trying to get an Access report to run in VBA by passing a variable from a recordset to the report using DoCmd.OpenReport but can't get it to work. Can someone let me know what I'm doing wrong? Using the specific assigned variable in the underlying report named “Code” with specific text works: "[Code]=""LXFT48""" But when I try to pass the corresponding recordset variable which is “FactCode” nothing is displayed in the report: "[Code]=" & "[FactCode]" Instead I’m asked for the parameter’s value so evidently it cannot identify the field. I tried substitutions for [FactCode] like [Model ..read more

I'm a beginner in SQL and database and I has been given a task to completely rebuild a project monitoring application we have here. The problem is, I only have access to the database (MS Access) because the app itself was coded in Delphi and I don't have the sources. Anyway, I check the MS Access database and choose to export it to SQL for a better performance and security but the way the DB was made... I'm not really sure it was the best way. A little example: column name data type ID_Project int electro nvarchar(255) Structures nvarchar(255) Collaborater ..read more

In MS Access I am trying to enable efficient data entry by allowing the user to specify a group of people as a singular list, then extrapolate this into a full list of records covering the range of pair combinations (rather than entering these all separately), excluding matching pairs. For example, 4 distinct Person_ID are observed at an event together on a given date. I would like the user to be able to enter the list of all the various persons involved, along with the date and other attributes; PERSONS | LINK TYPE | DATE Person_ID1 | observed with | date Person_ID2 | " | " Person_ID3 ..read more

I get an error when creating a query to the database, this is not my first query in the code, but it gives an error and I do not know what to do with it, if I create this query in the database itself, then there is no error, this is what the error looks like: "QODBCResult::exec: Unable to execute statement:" Error: "[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2." here is the code: QSqlQuery queryRequest(db); queryRequest.prepare("INSERT INTO ЗаявкаНаРемонт (id_клиента, Дата, Оборудование, Тип_неисправности, Описание_проблемы, Статус_ ..read more

I'm currently attempting to put together a form in Access that will piece together an email. As of right now the form works but it loads up one template file that I'm unable to make changes to via HTMLBody.Replace due to system security settings. Once the template loads, the user has to go through the body and delete the parts that are not applicable to the current situation. If possible, I would like to be able to split the template into multiple pieces and plug and play each piece based on the selections of the user. Is it possible to CreateItemFromTemplate() multiple times and combine their ..read more

I have 2 tables AudioInfo and Dev as shown below on MSAccess: 【AudioInfo】 has the following columns: file_id duration_sec 1 15 【Dev】 (short for Development Set) has the following columns: sourceid language 1 English I want to create the following table when AudioInfo.file_id = Dev.sourceid. file_id language duration_sec 1 English 15 I tried the following SQL query but results in an error: SELECT Dev.sourceid, Dev.language, AudioInfo.file_id FROM Dev INNER JOIN AudioInfo ON Dev.sourceid = AudioInfo.file_id; Could someone correct the above SQL to get the r ..read more