I've moved tons of text files or Excel files into Access DB's but I need to take an ODBC report and put it into a table on a new Access DB. If I save the report as a text file I get boxes when I import it to the new DB. If I use Excel I have to make a new table each time. Can anyone suggest the simplest way to take an ODBC report and put it into an Access table. THanks
View RepliesAttached is a screenshot of the relationships in a database I built a couple of years ago. It's worked absolutely fine in Access 2003 and currently has over 18,000 customers with associated information in it.
However, when I open the database in Access 2007 the performance is awful. All the forms are very slow to respond when tabbing between form elements. I've experimented by reducing the number of form elements calling on related data on a given page and whilst this improves performance it reduces usability - something I don't want to compromise on especially since Access 2007 should be able to cope with this.
My next question is therefore whether I've got the most efficient underlying table design and I can't see any other way of doing it than my current method so I'd be grateful for any feedback or advice anyone has.
I am having a problem with an access database that is connecting to a sql server. When trying to run any queries it is very slow. I think the problem might be caused by the ODBC link and I was wondering if there are any alternatives to using ODBC to connect access and sql.
View 5 Replies View RelatedHello all, is there a way to change an ODBC connection in MS Access. I have 4 ODBC connections that I can choose from (all of them from the same server), and instead of manually recreating a new ODBC connection everytime, I would like to point it to the new connection. The ODBC connections (Tables) have the same information, but throughout different times during the month.
Thanks in advance for your help.
Used to use these forums quite regularly and always found people to be of help, even started chipping in occasionally with the odd pearl myself :rolleyes:
Anyhow, having forgotten my username i'm back under a different guise and have a problem that i'd like some help with.
My company has a website running asp (not .NET) with an sql database backend. Basically we want a localised system to deal with order processing with daily updates to customer, order tables etc. I started to build something in VB and SQL but time is critical and once I work out this problem I can churn something out in access double quick time.
I can get the ODBC connection open to the sql database and import the data to Access 2007 table (ie dbo_tblOrders), but I then need to find/write some code to fetch extra records daily and append them to the EXISTING tables. At present data is fetched and added to a new table. Is it merge queries I need? If so how, and can I set it to only fetch new records?
Hi ,
I need to link some pages in my site to Access do I need to create ODBC for the connection ....
Hi
:) I am creating a program in VB.NET with an access 2003 database. This program will be in 2 physical locations. This is a program that sells tickets. At the end of the day I need to be able to get that days sales data off the remote location and update the main location with that information. This will be sales totals only. The user will plug in a thumb drive to move the data from one machine to the other. Due to the physical layout and other considerations this is the best option. The only action I want the user to have to do is to place the thumb drive in the USB port and hit the “down load” button. Then on the main location the user should be able to just plug in the thumb drive and hit up load. The database needs to be updated with the data from the remote location. What is the best way to do this?
Thanks in advance.
Hi All,
I have an Excel file with multiple worksheets using VLOOKUPS betweent he sheets and various formulas within the sheets, mainly basic arithmetic and also ROUNDUP.
I am wanting to transfer all this over to Access so that it can be edited by 2 computers at the same time and I need all the formulas to go with it. What's the easiest way to do this? I'm not a very experienced Access user and basically have no idea how to put in formulas in Access.
Any help would be greatly appreciated. I am happy to email to the excel file to anyone who wants to take a look.
Thanks,
Michael
Hi,
I want to import into an MDB table a csv file.
I'm trying to use the bulk copy table.
my function is:
SQL = "SELECT * INTO [my_table] FROM [ODBC;Driver=Micrsoft text driver (*.txt; *csv) ;Dbq=c:\;Extensions=asc,csv,tab,txt;].table.csv"
db.OpenEx( "Driver=Microsoft Access Driver .mdb);DBQ=c:\access.mdb;", CDatabase::noOdbcDialog );
db.ExecuteSQL( SQL );
when i run this function i get an error : "You cannot use ODBC to import from, export to, or link an external Microsoft Jet or ISAM database table to your database"
when i try to import in the same way a dbf file (insted the csv file) with VFP it's working well.
what seems to be the problem? how can i fix it? or if some one know how can i import a large csv file into access DB in an efficient diffrent way?
thanks ishay
I finally got a chance to test the setup remotely, and it basically works with all the links I've set up.
I wanted to see how it would behaves should it disconnect or something and thus manage it gracefully.
First, I broke the network connection and tried to navigate to next record. Instead of getting a error message, Access just crashes. I'm not sure if this can even be trapped at all.
Secondly, I disconnected the connection then reconnected it. Access then tells me that the server has gone away, presuming because it's still looking to the old connection that was broken. Running the code to repair link fails also with same error. Can this be resolved with having to quit & restart Access?
Thirdly, I put a breakpoint on Form's Error Event but this doesn't seem to always to catch all errors. It does catch error if I tried to start up a form without the connection, but if it already has been started, and the connection was broken, the Error Event is never fired even though I get a dialog saying ODBC Call failed, then a zillion of dialogs saying Object no longer set or invalid. This would be extremely annoying to my end users.
Can any of those be avoided or fixed?
I've just set up my Access on a new PC and now when I try to connect to my MySQL database through odbc, Access crashes without any error messages, just the standard microsoft error reporting message.
I'm using Office 2003. What do I do??
I have a form which I want to move OnLoad to the top-right of the Access window. Something like this:
Private Sub Form_Load()
Me.Move Right:=150, Top:=150
End Sub
But there isn't a Right:=, only Left:=. So maybe I could find out the width of the Access window? Is this possible?
Thanks for any advice.
The data I have in excel is basically everything about the "site". In access this cant be stored in one table.
What's the best way to move data from excel into access across multiple tables? I was hoping I could create a query and assign the columns from excel to the columns in the query. but I cant see an option for this.
I am treying to move data from an Excel worksheet to Access. My code is below. My program does not like my "Set rs = OpenTable("GL")" line
Option Explicit
Public Const gconConnection = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:usersc156281my documentsPLANPlan New.accdb'"
Public rsData As DAO.Recordset
Sub PostData()
'TryPostAgain:
'On Error GoTo BadPost
[Code]...
Hi m8
I have Microsoft Access 2003 which is running fine. I have setup ODBC source through windows xp Professional control panel.
While i am in access application and try to open by selecting
File/Get External Data/Link Tables
It open Link PopUp Window, then from the Files of type drop down, I select ODBC Database(). Windows just disppear, not opening odbc source selection
window.
Please could you help me, why this is happening and sort it out.
Many thank
Please help...
Database Results Error
[Microsoft][ODBC Microsoft Access Driver]
General error
Unable to open registry key 'Temporary (volatile) Jet DSN for process 0xcac Thread 0xcc0 DBC 0x1090064 Jet'.
The code: example.asp
Code:<%fp_sQry="SELECT count(*) FROM TEVPDATA where ((datediff('d', '" & date() & "', DT3PROF1) between 0 and 30)) and (COL2 = 'PROF1/SLM')"fp_sDefault=""fp_sNoRecords="<tr><td colspan=1 align=left width=""100%"">No records returned.</td></tr>"fp_sDataConn="Fabos"fp_iMaxRecords=0fp_iCommandType=1fp_iPageSize=0fp_fTableFormat=Truefp_fMenuFormat=Falsefp_sMenuChoice=""fp_sMenuValue=""fp_iDisplayCols=1fp_fCustomQuery=TrueBOTID=1fp_iRegion=BOTID%>
Global.asa:
Code:'--Project Data ConnectionApplication("Fabos_ConnectionString") = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=data/Fabos.mdb"FrontPage_UrlVars(4) = "Fabos_ConnectionString"Application("Fabos_ConnectionTimeout") = 15Application("Fabos_CommandTimeout") = 30Application("Fabos_CursorLocation") = 3Application("Fabos_RuntimeUserName") = ""Application("Fabos_RuntimePassword") = ""
I am working on an Access 2002 database where one of the tables has five required fields making up the key.
There is a form that is linked to this table which is used for adding new records.
There is a close button on this form that has the following code in it's OnClose event:
"DoCmd.close"
When the form is opened, no data entered and then the close button is clicked - nothing happens.
It used to work fine, until I recently upsized the database into a SQL Server database and now it's all gone horribly wrong.
If I close the form manually by clicking on the cross, I get this
error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value
NULL into column 'Product', table
'LeasebookSQL.leasebook_user.leases'; column does not allow nulls. INSERT fails. (#515) [Microsoft][ODBC SQL Server Driver][SQL Server] The statement has been terminated. (#3621)
Can anyone please tell me how I can get the form to close without trying to save the record, especially when no changes have been made to the data on the form?
Thanks,
Simon
I have 5 fields(First,Second,Third,Sum,Sum1). The Sum field is the sum of the first, second and third. In excel I am using the following formula/expression to calculate the Sum1 field =IF(E1<0,E1+10,E1+0 - and it brings back single digit.
e.g.
First=3, Second=5, Third=8, Sum=16, Sum=6
Can I do this in an Access query, if so how?
Thanks in advance for any help.
How can I import data from excel to access, i have a huge file more then 5000 entries in there....
View 1 Replies View RelatedI'm using Access 2007 which I built a database that runs queries off of tables using ODBC. The database is all done in ms access, I've used no visual basic programing on it. I'm now trying to move a copy of the database to another computer's local drive in the office. When we try to open it on the other computer it gives me an error that says "There was a problem sending the command to the program" and Access hangs, which I then have to run task manager to end the program.
Specs Are: Windows 7 64bit (the db was created on Windows 7 32bit)
Microsoft Access Version 2007
Currently I have an Access dB that was developed in A2003 but we are currently using A2010, it is accessed by multiple users that all have a frontend locally on there own networked PC's, with the backends etc on an old NT server, we are having problems with new technology accessing the NT server so it's well past time we got shut of it
We have a share on our network that has been made available to us to migrate all our NT server files to, Here's my problem, on testing the procedure I copied the database backend and .mdw file over to the share then tried to open the dB where upon i got the following error message
Cannot open microsoft Access database information file
On further investigation it seems that both the back end and the .mdw file have copied over with no content i.e filesize is 0kB, not only that the originals also seem to have had there content "deleted" a simple copy shouldn't have done this (I did have a backup btw)
I've build an Excel workbook that contains extensive use of VBA code for UserForms, Control Buttons, etc. and also contains several sheets that are formatted as Tables and function as databases. We've outgrown Excel's capabilities and need to move over to Access.
View 1 Replies View RelatedI have a Access front end connected to a MS SQL database using a ODBC connection which is working fine of course.
When I compile it as a runtime version and install it on the business computer I get an error with the ODBC connection (I can't remember exactly what pops up). On the business computer I have installed SQL Native Client and in the ODBC added a System DSN (which works).
I am in need of consultation for MS Access reading data from ODBC connection. I have SQL Server that has all the data for the project financials etc.
I need a database that will read only certain data from the tables, for example, I don't need to import all 500,000 lines from SQL through ODBC connection, I just want to bring certain data for a list of projects whichever are opened and load only that data in MS Access so the group then can add additional details for that project in a shared MS Access.
Right now, all I can do is connect to that database through ODBC and brings all the data which I don't need all as it increases the size of the database, but just a criteria to specify which data to bring, if that's possible to do.
Hi,
My access database's tables are currently linked to an excel document. My question is, how do I move my database to the network? Currently when I move the the database to the network no one can open my tables because the link goes to my documents on my computer which they can't access on the network.
So how do I move the database and maintain the link between the database and my tables?
Thanks in advance,
Ryan Smith
I have a form in which user will enter few values but I need users to enter the comment in relevant text box too only if they have any non zero value.
I have attached an example for reference....where red encircled portion is for values and blue is for relevant comments. If value is non zero then user should be enforced to enter the comment too......system should not allow user to move forward or backward until there is some comment.