I am facing a strange problem off late with the MS Access database. After Insertion of records, I close the recordset and not the connection as I use a global connection, which is set at the time of the app launch. Now, after closing the recordset I move on to the next job in the program flow and return a success to the calling function. Once the value is received by the calling function it opens the same table and finds no records there. I could come around this prob by providing a delay of a few seconds and then returning the value to the calling function. Can someone help me understand this prob and the solution to this?
I have an Access front end to a table residing on an SQL server. I am accessing the SQL server through a VPN over a DSL line.
Now, I have some data I need to insert in to that table. It sits in a text file. All in all, there are around 9000 records in the text file.
I had excel reading the text file, then cleaning up the data, then I would link the excel table via ODBC, and link to the SQL Server table, then do an INSERT INTO ServerTable Select * from ExcelTable; That would take about 22 minutes to finish.
I cleaned things up a bit, using VB to directly read the text file, and I can insert in to the ServerTable directly via an ADO recordset in about 18 minutes.
I want to get the time down further.
Now, if I read that text file and use ADO, I can insert all the records in an Access table on network share across that same VPN in about 4 minutes, so I figure there is about 14 minutes of overhead in dealing with the SQL server.
So is there anything I can do to decrease the amount of time it takes to insert the 9000 records in to the ServerTable?
Thanks, David
PS. Oddly, I tried to limit the number of fields I data I upload, and found it actually took MORE time to send one field than it did to send all 30 some fields in the full dataset. I really can't figure that out.
Hi! I'm stuck and need some help. I have a list of about 1600 entries, all merchant names and i need to place an * at the beginning and end of each entry, example *Arizona Central*. Each entry has a different amount of characters...would any one know if there is a way of how I can do this easily in Access or Excel?
Hi everyone, Im new here, and wanted to get some help on understanding insertion anamolies, because the logic behind it I can't grasp, because I tend to think of work arounds to solve the issue, so I am wondering whether I could get some help on the matter.
Does anybody know of a quick and easy way of preventing data insertion to a table from a form, so that table doesn't get updated unless all form fields are populated?
I have a form that is opened by the following code: Private Sub Status_AfterUpdate() If Status.Value = "WIP - Snagged" Or Status.Value = "WIP - Suspended" Then DoCmd.OpenForm "NotesJobChanged", , , acFormAdd Forms!NotesJobChanged!txtJobNo.Value = Me!txtJobNo.Value Forms!NotesJobChanged!Date.SetFocus End If
This 'NotesJobChanged' form has three text boxes on it - JobNo, Date & Note. The JobNo is autofilled from the code above. If nothing is entered into the other fields and the form is closed this record is entered into the database with only the JobNo filled in.
What I want is a way to force the user to enter data into these fields. I don't want to set the 'Required' property of the field to yes as it generates an awful system error message. If the user attempts to close the form without filling it out can they be reminded that they have to, but with an option then to close the form if they really don't want to enter any data? If they do chose to close without filling it out can it be prevented from being added to the database.
I have a form that when I insert a registration number I would like the value of the field "nom" the name automatically appears in the "nom" field of the form. The form uses a query.
I've run into a bug, I think. I have a time field with it set to show "Medium Time" which is "hh:mm(AM/PM)", however, when I combine the information with another field in a query (=[OfficeDay]&" - "&[POfficeTime]), the time comes out as "Long Time" or "hh:mm:ss(AM/PM)". End result is Wednesday - 10:30:00AM when it should be Wednesday - 10:30AMDoes anyone know a way around this?
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
I want to know more about the MS Access Run time edition. If I prepare pakage in MS Access and installed in one server, shall i share the forms in client computer using Run time?
Ok i have a few issues with my database, basically i have a form with a subform running from two tables. These tables are to record faults and the subform holds any updates and progress made.
Basically i am trying to make a facility that only prints out the current job being looked at in form view, but i want it to print in a printable view as apposed all the graphics on the form.
As well as this i have created a form and on this form i have a combo box which contains equipment! how do i create a filter type thing so when i select the appropriate equipment and run a macro it gives me a report only on the forms with said equipment.
I am poo at SQL and scripting so if anyone can help me i will be very happy!
Hello, I have a roof-estimating program that I made in MS access 97. I want to sell it to online. However the buyer will have to have access 97 installed on their computer to be able to use my program. Is there a way to allow users to use my program with out them having access 97 installed on there computer? And if so how involved is it to make this work?
I’ve been reading about the access developer’s extensions for access that makes a run time version of an access program. Will this work in my situation. I’m using access 97. I've seen some web sites with run time installers. Do these programs really work like they say?
Will I need to re-write my program? And if I do need to re-write it, is there a certain way that it needs to be written in order for this to work.
I have a rather large project that has been developed in Access 2000. Generally all my distributions have been to PC's with Access available. However, I do find that there are a few that don't. Obviously this is where Run time would be useful. Having never used it I have a few questions if someone could possibly help:-
1 If I have a program developed in Access 2k can it be converted to the MOD version to include runtime? 2 If there is a conversion process is it automatic or does it require extensive reworking of the project? 3 Is it still possible to purchase MOD 2000 or do I need to get an updated version and if so will that still work with Office 2k?
Hi, I am just learning Access and are having problems dealing with time intervals. My database form is seperated into two tables and in each column of the tables there are different time periods. The time periods are created by having two columns and one column is the start time while the second column is the end time.The problem is that in order to tie the two database together i have to group the database use the time periods as a key. But since the two tables time periods are different, i have to first seperate time intervals to specific months. My plan first is that using queries, i will insert new rows and in those columns i will have both the start month column to end month column both be the same month, but i could never figure out how to duplicate my information and change only the month. Now i am at a loss and i really need help for the project. I am sorry for the poorly worded explanation but i can't think of a easier way to say it. Please advise me on what to do and thank you for reading.
I was wondering if anyone would be able to help me here.
I have a database created in Access .MDB. It operates allright on my machine. However, since this database is done for my client, i have a problem i need to ask for help.
My client doesn't have microsoft office software installed on their machine. So when i passed the database onto them, they somehow can only run it in the run-time environment. Don't ask, I'm not sure about this at all. Anyway, They came back to me saying that they have got a run-time error message in the middle of running the database.
On my end, it's hard for me to debug since it works fine here. I was wondering if anyone would be able to tell me how i could have this database run in the run-time environment as my clients do. This way, i could debug the problem.
Is it possible for me to run my access database in run-time environment? how do i do that?
Hi all How are you? I have a small problem... I have an access db called MainDB, which has tables, queries, data entry form. This db (MainDB) is located in the network server. I have another access db called BranchDB. This db (BranchDB) has no tables, it has linked tables to those tables from the MainDB, and it has the forms for view only (No Data Entry). The problem is that users from there local machines can not access the BranchDB at the same time. What can I do? Your help will be highly appreciated.
I'm using MS Access 03 to run some queries on a SQL database. 2 of the columns i pull from SQL are about time, but are stored in 'unix time'. can this be converted once in Access to a more readable format eg ddmmyyyy?
I am constructing a databse for Time and Attendance and wish to have a cumulative total column for the hours worked in the month, the Time in/out, lunch and TotalHours fields are using the ShortTime format but when the total hours exceed 23:59, obviously the field will not accept this Data, any ideas how i can record the TotalHours worked in a month. Many thanks in advance for any suggestions.
I’m trying to find a way to convert a UNIX timestamp to the standers universal timestamp. At this point I can’t even make sense of the UNIX time stamping system.
I would like to be able to use Microsoft access to do the conversion.
Does anyone one a why to convert the UNIX timestamp into a MMDDYY formatted time. Any help would be greatly appreciated
I have created a database then saved it to my computer. Now while I am trying to open MS ACCESS in my computer I am getting an error message -
"This file may not be safe if it contains that was intended to harm your computer. Do you want to open this file or cancel the operation?" Giving the options - 'CANCEL' 'OPEN' 'MORE INFO'
-------------------- Jeff Conrad - Access Junkie - MVP Alumni SDET - XAS Services - Microsoft Corporation
Co-author - Microsoft Office Access 2007 Inside Out Presenter - Microsoft Access 2007 Essentials http://www.accessmvp.com/JConrad/accessjunkie.html Access 2007 Info: http://www.AccessJunkie.com
---------- This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.mspx ----------
