ms-access

Create Access databases programatically through vb.net

Let me preface this by saying that I know this is a stupid way to go about this, but it needs to be handled in this way. I need to make an application that from a master database creates a number of access database files (tables of a larger db), then these are manually given to users who fill in data, the database files are emailed back...

how to automate upsizing from Access to SQL Server?

Hi, I need to automate the migration from an Access (2003) to an SQL Server DB (2005 or 2008). The upsizing should be done automatically as part of a build process. I need that because there are 2 versions of the software, a single user rich client and a web version. Access DB is used for single user to minimize setup effort, SQL Server...

sql insert query needed

Hey guys, so I have two tables. They are pictured below. I have a master table "all_reports". And a user table "user list". The master table may have users that do not exist in the user list. I need to add them to the user list. The master table may have duplicates in them (check picture). The master list does not contain all the inf...

What is the correct "Document Name" of an Access ADP/MDB to use in the GetObject("Document Name") call?

According to http://support.microsoft.com/kb/288902/en-us You can attach to a specific instance if you know the name of an open document in that instance. For example, if an instance of Excel is running with an open workbook named Book2, the following code attaches successfully to that instance even if it is not the ear...

ms-access: specifying formatting for a column

i would like all data that is entered into a specific column to always have a trailing comma and also i need the first character to always be a comma. how do i do this? the best solution is probably not in vba or sql but probably the properties of the table? ...

Grabbing "last saved by" metadata from access '03 file to excel '03 in VBA

So I am in Excel trying to read the last saved metadata from an Access database, but I cannot figure out how on earth to do this. If I use environ$("username") it will give my the last saved information for the excel document where I am coding, not the access db that I want it to grab from. Excel has a built in function for grabbing us...

Docmd.TransferText to update data

i am using Docmd.TransferText to import data from a text file into my access table. i would like it to do the following: if the record already exists, then update it if the record does not exist then add it how do i accomplish this? currently i have this line: DoCmd.TransferText acImportDelim, yesyes, "table3", "C:\requisition_dat...

read text file line by line and insert/update values in table

i am exploring the option of whether DoCmd.TransferText will do what i need, and it seems like it wont. i need to insert data if it does not exist and update it if it does exist i am planning to read a text file line by line like this: Dim intFile As Integer Dim strLine As String intFile = FreeFile() Open myFile For Input As #intFile ...

if exists, update, else insert new record

i am inserting values into a table if the record exists already replace it, and if it does not exist then add a new one. so far i have this code: INSERT INTO table_name VALUES (value1, value2, value3,...) where pk="some_id"; but i need something like this if not pk="some_id" exists then INSERT INTO table_name VALUES (value1, v...

Reverse engineer Access macro from .NET

A co-worker has inherited a Microsoft Access macro that executes a series of SQL operations against a Sybase database by calling OpenQuery on a bunch of action queries, which use linked tables over ODBC. I would like to help him convert it to an SQL script that we can execute directly against the database. And since we have quite a few m...

solution for updating table based on data from another table

i have 2 tables in access this is what i need: 1. if the PK from table1 exists in table2, then delete the entire record with that PK from table2 and add the entire record from table1 into table2 2. if the PK does not exist then add the record i need help with both the sql statement and the VBA i guess the VBA should be a loop, going...

Invoices for Printing in MS-Access

The application that I'm currently working on has a funky set up for the invoices that they print. The form is the invoice that is printed out. I took a look at the Northwind DB and what it does is and it actually generates a report based on the record's information. What are the limitations of using Forms vs. Reports for printing out ...

IN 'mytextfile.txt' syntax - access

i would like to use this syntax to update a table in access based on data from a txtfile. fenton in his comments on this answer: http://stackoverflow.com/questions/2992168/read-text-file-line-by-line-and-insert-update-values-in-table/2992337#2992337 said that this is possible and i would like to see the exact syntax please ...

How to Solve DLL error in access

I am developing an Excel project in Access using VBA. My project is working in Access 2007, but it does not work in Access 2003. If I try to run the file, it shows the DLL error. Anyone know how to debug this error? ...

Determine used DAO version

Simple (?) question: how can I determine the used DAO version in VBA code? ...

What is a way to access .accdb files for reading and writing?

I have an Microsoft Access Database (.accdb) that stores information needed in an application. Is there a way to distribute my application with the access database without having the user have access installed or get the user to go and download and install the office runtime? The windows form application that uses the access database i...

Is it safe to ignore ~sq_ queries when using LoadfromText

I'm working on some VBA to do exporting and importing (using SaveAsText and LoadFromText) of MS-Access objects in an application I'm supporting. I'm hoping to be able to start storing my code in SVN so that more than one developer can work on this application at once. I keep getting the following error when I bring in queries that start...

Word mail-merge called from Access 2007

Hi, I am working with a french version of Access and I absolutely need characters with accents (, etc.) I am calling Word from Access to do a mail-merge. I used to output the result of a query in a RTF file and merge with a .dot file. With 2003, the whole process went OK. With 2007, the accentuated characters go wrong. I tried UTF-8 enc...

clearing an entire column in access

is there a way to clear an entire column in a datasheet in access? i can just right click on it and delete it but that will affect the structure, i just need to clear all the records. how do i do this? perhaps the question i should be asking is how do i clear the entire contents of a datasheet in access? ...

treating paramater as literal

DoCmd.TransferText acImportDelim, Import-Accounts, "tableImport", _ "C:\Documents and Settings\accounts.txt", True The second parameter: Import-Accounts is the actual name of the saved import specifications. supposedly it does NOT need to be in quotes; however in this case since there is a - there it is treating it as if i were doi...