i would like to create an excell file (and choose the path) in ms access
and fill that excell file with data divided over multiple sheets
or even if possible take an excell template and fill it with data, then rename
and save it
any suggestions?
Hi all, I'm trying to get the following done:
I have code which import Excel files into my database with the "DoCmd.TransferSpreadsheet". which works great.
But I'm now splitting the database and I want to have the Excel files which a user selects in his frontend database, imported in my backend database. I don't see the possibility in the TransferSpreadsheet as this is set to the CurrentDb.
My temporary solution is to import in the frontend and CopyObject to the Backend, but does anyone have a direct solution?
Thanks, Rob
I am currently using access 97.
I am trying to read from an excel file that has the same format but changes data every day due to reports that I run from a different program. I just export them to this excel file called test.xls
What I want to do is import this data from the excel file and insert it into existing data in my table. I need to this every day.
Currently I cannot get the data to append. I can only insert the data once and cannot get it to append. I tried using macros, but think it would be better to use VB.
Any help you could provide would be great.
Thank You
Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.
TBL_NEWDATA { Person_ID, Department_ID }
TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID)
SELECT Person_ID, Department_ID
FROM TBL_NEWDATA
WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
hi!
I've got a database with about 200 tables and have to make sense of it. There
are no relationships and it is a little bit difficult to understand this mess. Is there any
way how I could export only the table descriptions into an excel file? I want to end up with two columns.
The first should contain the table name, and the second should contain its attribute/field names.
Bit like:
table1 attribute1
table1 attribute2
table2 attribute1
...
I'm new to access so it would be very nice if you could explain any steps in some detail.
Thanks already!
G'afternoon to all, I was wondering if someone can help me code a Command button named "Export to Excell" on form frmEdit. I need to export a query named qryTagnumber onto an excell spread sheet, Also if it isn't that hard and if it can be done, I would like to change the rev of the file name everytime I hit the button. Can this be done? Please see attached database. The Modules had to be removed for confidentiality of my company. These don't have to be used to run the program. They are just used for String functions. I look forward to your responses. Thanks...
Scott
Super new to the forums and hoping I can find an answer here.
After looking for an hour or so on Microsoft's site, I gave up and decided to venture into new resources.
My ultimate goal is to create a form that will prompt a user to select a Microsoft Excel file and then run a macro. The macro should import the Excel file into a single table based off of what form is being run. The idea is to have a single form for each employee of a specific department and have the form import information that is stored on our phone server into access. I've set up most of the macro but the argument section of the filename is what's hanging me up.
So finally my question;
Is there a way to have a macro run to specify what file you want to import when using an additional macro? Am I going about this the completely wrong way and should stick to VB Code?
I look forward to your responses, thank you in advanced!
I need to insert(append) first and last line in xml file...
So I have xml file and I need to insert one line to the top and at the of the text... Xml file contains Cyrillic characters and method to recreate xml file is going wrong with characters conversions... The last line a can append easy but the first line is a problem...
Hello everyone and happy New year.
Ii have a database running on Access 10.0, I am using the insert hyperlink dialog box (access by Ctrl K) to allow users to insert a hyperlink to a file (could be any sort of file), into a text box.
The problem I have is most of the files are held many directories above where the database is (we have a BAD directory policy, 10's of folders within folders) and it gets a little painful to get the user to go up so many levels, due to the hyperlink dialog opening in the "current folder" view, ie where the db is located.
Simple quest, or so I thought. Can I change the default directory "Current Folder" that the dialog box starts in ?
I have already tried chdir"c:" but to no avail.......not that obvious then...
All help greatfully received...
Any help greatly received......
Hi All
I hope someone in this forum can help me with this databse issue:
I want to load text file to .mdb MS access file.
I understand that BULK INSERT is possible only when using SQL Server database - not MS Access like I do.
Can someone advice what can be done ?
Can you think of an alternate solution, which is also effective (not inserting each record one by one) ?
BTW - I'm using ADO on visual studio 6 in order to access the database (not ADO.NET).
Thanks
i have a table of calls, with a field called "link to file".
i have a form where staff fill in their calls and when they have completed the call there is an option to insert a hyperlink to where the file is stored.
i previosuly had an attachemnt there but the database would just grow and grow so now its prefered there is a link to where the files are kept.
i just dont know how to set it up, i have tried adding in a text field and setting it to hyperlink which does work but on the form the user has to right click the button and edit hyperlink to place it in - is there an easier way of just clicking the button and it brings up a folder location where you select the file and it saves it as a hyperlink into the table under linked to file?
hi,
i have to create one Master Data transformation tool use Microsoft access. The input file and output file of this tool are excel spreadsheet. There are 2 levels of the data transformation, first level is data mapping and validation on the raw data, second level is data update on the field value.
My question can someone show me step by step how to build this tools using access and read from excell.
I like to do following task using access VBA:
1. Open outlook.
2. Select word file(with tables and graphics) and insert into new outlook email.
I have an Access database. Now I want to export the data of a table in the database to excel sheet in pre-defined format. e.g. i want that in the first row of the excel file, there is a heading called "Report". In the second row, there should be column header like "Name", "Age", "Gender". Also there must be 1 column gap between this header. Then in the following rows, there should be data.
View 2 Replies View RelatedWhat I want to do is inset a html file into the body of the email . I have created a publisher file and saved as html ..say
C: estpub1.html
How do I get this in to the body ?
Code to date below (not the tidiest - but it works)
Private Sub Command0_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim OutApp As Object
Dim OutMail As Object
Dim strAttach1 As String
[Code] ......
I am trying to use SQL to run queries in our access database in order to (hopefully) speed things up. I'm trying to create code that basically takes data from one table and inserts it into another whilst doing calculations on the data.
However I can't get past this:
Code:
Private Sub Test_Click()
Dim strSQL As String
strSQL = "CREATE TABLE [TempRedAmberGreen]" & _
"AS (SELECT " & _
"[ID_CHK] String," & _
"[Red] String," & _
"[Amber] String," & _
"[Green] String)" & _
"FROM [035 - Meter Point HH Data];"
DoCmd.RunSQL strSQL
End Sub
It keeps saying "Run-time error '3292': Syntax error in field definition.
I often create contract using mail merge. I have an access file that I want to use as data source for word file. But it does not automatically.
Please download the attached file !
If there are 1 customer and 1 property, I do not need to do anything. Conversely, if there are many customers and many properties, I take time to manipulate.
Firstly, I open the word file. I have to copy and paste paragraphs that I want. Highlight of the original paragraphs is blue.
Secondly, I click 'Insert Word Field' -> select 'Next Record'.
In short, I wish to use VBA in access file to automatically perform the steps that I have outlined.
I'd like to create a query that would do something like the following:
If Code="A" then ProdType="Accessory"
If Code="BS" then ProdType="Blank Stock"
etc.
Can this be done without creating a query for each instance?
Thanks.
HI all-
I have a list of INSERT statements (SQL DML) and I wish to insert this data into an MS Access 2000 table
Unfortunately I cannot find a mechanism to accomplish this in Access. Does one exist? How can I import this data into access in a SQL format?
Thanks in advance!
When the IVA_MEMBER_ID field has no data I would like this query to return : 000000000,
is this possible and if so could you show me how? Thanks
SELECT DISTINCT
NOLDBA_INT_MEMBER_DEMOGRAPHIC.IVA_MEMBER_ID,
NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEM_SSN,
NOLDBA_LOG_DISBURSEMENT_DETAIL.AMT_DISBURSE,
NOLDBA_LOG_DISBURSEMENT_DETAIL.CD_TYPE_DISBURSE,
NOLDBA_LOG_DISBURSEMENT_DETAIL.SEQ_ORDER,
NOLDBA_LOG_DISBURSEMENT_DETAIL.SEQ_OBLIGATION,
NOLDBA_LOG_DISBURSEMENT_DETAIL.ID_CASE INTO [A TBL]
FROM NOLDBA_LOG_DISBURSEMENT_DETAIL INNER JOIN
(NOLDBA_INT_CASE_MEMBER INNER JOIN
NOLDBA_INT_MEMBER_DEMOGRAPHIC ON
NOLDBA_INT_CASE_MEMBER.MEMBER_ID = NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEMBER_ID) ON NOLDBA_LOG_DISBURSEMENT_DETAIL.ID_CHECK_RECIPIENT = NOLDBA_INT_CASE_MEMBER.MEMBER_ID
WHERE (((NOLDBA_INT_MEMBER_DEMOGRAPHIC.MEM_SSN)>"0") AND ((NOLDBA_INT_CASE_MEMBER.RELATION_CODE)="C") AND ((NOLDBA_LOG_DISBURSEMENT_DETAIL.CD_CHECK_RECIPIEN T)="1") AND ((NOLDBA_LOG_DISBURSEMENT_DETAIL.DT_DISBURSE) Between [START DATE] And [ENTER END DATE]))
ORDER BY NOLDBA_INT_MEMBER_DEMOGRAPHIC.IVA_MEMBER_ID;
I want to set my insert such that
Insert into tableA (x,y,z) = (SELECT x,y,z FROM tableB)
WHERE NOT EXISTS (select x,y FROM tableA,tableB WHERE tableA.x = tableB.x AND tableA.y = tableB.y)
Basically I want to insert data in tableA from tableB if it does not exist in tableA yet.
Access says I'm not writing an updateable query.
Hi experts. I want to learn how i can insert data in to external db using vba.
For example , i want insert table names in to the external db.
(A command button in a form )I be happy if some one show me how. Thanks
why do i get all the weird problems?
does anyone have any idea why a textbox would act like it was locked, even though it's not?
anyway, the text box works fine, until i click my button which goes to a certain record based on my listbox. the code for the click event is as follows:
Code:Private Sub cmdFindProcess_Click()blnexist = True Dim rst As dao.Recordset Dim strSearchName As String Set rst = Me.RecordsetClone strSearchName = Str(Me!List26.Value) rst.FindFirst "ID = " & strSearchName Me.Bookmark = rst.Bookmark rst.Close If Me.List26.ItemsSelected.Count = 0 Then MsgBox "Please select your process", vbOKOnlyElseMe.formattedtimeelapsed.Visible = True Me.StartTime.Visible = True Me.EndTime.Visible = True Me.btnStartStop.Visible = True Me.btnStartStop.SetFocus Me.StartTimeLabel.Visible = True Me.EndTimeLabel.Visible = True Me.InProcess.Visible = False Me.Current.Visible = True Me.NewProcess.Visible = False Me.hidID.Value = Me.List26.Value Me.formattedtimeelapsed.Value = "00:00:00" Dim Rc As dao.RecordsetDim Db As DatabaseDim SQL As StringSQL = "SELECT * FROM StopwatchRecord Where StopwatchRecord.ID=" & Me.List26.Value & ";"Set Db = CurrentDb()Set Rc = Db.OpenRecordset(SQL)Rc.MoveFirstMe.hidID.Value = Rc.Fields("ID")TotalElapsedMilliSec = Rc.Fields("ElapsedTime")Me.ElapsedTime.Value = Rc.Fields("ElapsedTime")Me.Comments.Value = Rc.Fields("Comments")Me.StartTime.Value = Rc.Fields("StartTime") Me.txtProcessDisplay.Value = Me.List26.Column(3) Me.txtActNumberDisplay.Value = Me.List26.Column(1) Me.Comments.Enabled = False Me.txtAdditionalComments.Visible = True Me.txtAdditionalComments.Enabled = True Me.txtAdditionalComments.Locked = False End If End Sub
my guess is that the "bookmark" code is not allowing it. i'm not sure however, how i could find the record any other way, if that is the problem....any suggestions would be greatly appreciated.
thanks in advance,
*j
I have a form with a sub form, on the main form I want the user to select a machine number and then when they have selected it I want to populate the required data into the sub form.
In the sub form I have a field called "Seal" and another called "Quantity" what I require is to get the results from either a query or table and list all the seals & quantities for that machine.
I have a table called "Machine" which has all the machine numbers and also fields called "Seal1", "Seal1Qty","Seal2, "Seal2Qty" all the way to Seal 10.
I do not know how the best way to get this data into the sub form...
Hi,
I have a form which records computer faults reported by users. On this form there is a subform for comment tracking eg. Phoned user, Date/Time. On the next line would be; Informed user to do this, Date/Time and so on. I have a button on my form which I would like to use to automatically put in a line of data. It is an aknowlegde fault button. What it hopefully would do is when you click the button it will automatically put in the date/time in the subform, Then the word "acknowledged" in the comment field, then prompt for the user name ( for the username field).
I am not sure on how to start the command. If i were to do it in the same form then I would just put something like;
me.Date.Value = Date()
me.Comment.value = "Acknowledged"
me.Username.value = "[Prompt]"
As you can see I have two problems though;
1. The data has to be entered in a subform
2. My Prompt I dont think will work.
Does anyone have any ideas?
Kind Regards
Keith
when I am exporting in fixed width format (.txt file), is it possible to automatically include in the outputted text file:
a header of the current date and time, and
a footer that will say how many records are in a certain table?
edit: when i say header and footer, I mean the first and last entries in the text file
thanks
Venom