MySQL Forums - Microsoft Access

Changin to Mysql from Access (no replies)

Mickael Wenders — Tue, 24 Nov 2009 18:03:41 +0000

Hello to all,

I have been using Access. We are changing to Mysql. We have an important load of customer data on Access.
Could you help me on this change?

Thanks for your feedback.

I NEED YOUR HELP PLEASE (no replies)

Gabby Takash — Sat, 21 Nov 2009 14:33:34 +0000

Dear Database Support Professional,

I am Database Administrator and I am writing to ask for your help in responding to a 10-12 minute survey for a degree requirement on relational database support professionals in the USA. Please note that the respondent is completely anonymous. If I don’t get this portion of my degree completed by the end of this semester, then I would have to wait for another year which entails additional costs and time on me. By completing the survey, you will be contributing to research in our field and also there is the satisfaction of having helped a fellow professional and a fellow human being attain his goals. I believe a good turn always bring another in some way in our lives. I therefore kindly beg for your help to a fellow database professional by completing this survey. The link to the survey is provided below. If possible please forward to other database professionals or groups:
http://www.surveymonkey.com/s.aspx?sm=a1JqU7GVt1g6rCkIPyL23w_3d_3d

Thank you earnestly for your kindness.
Gabriel

The connection to the target database could not be established (1 reply)

Michael Euler — Tue, 10 Nov 2009 13:39:33 +0000

Hi,

I've tried to migrate a MS-Access-Database to MySQL. Migration-tool, MySQL and the Access-database are on the same machine, with a localhost installed using the XAMPP-Package.

All setting are according to the manual, however the connection to the target-database cannot be estabilshed. Following details are provided:
---

Connecting to source database and retrieve schemata names.
Create a dummy schema list because Access only has one schema for each file.
Return schemata list.
Schemata names retrieved successfully.
Initializing JDBC driver ...
Driver class MySQL JDBC Driver 5.0
Opening connection ...
Connection jdbc:mysql://localhost:3306/?user=root&password=xxxxxxx&useServerPrepStmts=false&characterEncoding=UTF-8
The connection to the target database could not be established (error: 0).
ReverseEngineeringMysqlJdbc.getVersion :Unknown initial character set index '48' received from server. Initial client character set can be forced via the 'characterEncoding' property.
Details:
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
com.mysql.jdbc.Connection.configureClientCharacterSet(Connection.java:2345)
com.mysql.jdbc.Connection.initializePropsFromServer(Connection.java:3913)
com.mysql.jdbc.Connection.createNewIO(Connection.java:2683)
com.mysql.jdbc.Connection.(Connection.java:1531)
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
java.sql.DriverManager.getConnection(Unknown Source)
java.sql.DriverManager.getConnection(Unknown Source)
com.mysql.grt.modules.ReverseEngineeringGeneric.establishConnection(ReverseEngineeringGeneric.java:141)
com.mysql.grt.modules.ReverseEngineeringGeneric.getVersion(ReverseEngineeringGeneric.java:161)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.mysql.grt.Grt.callModuleFunction(Unknown Source)

---
I'm sure it is a simple thing but due to lack of experience I'm not able to solve the problem by myself. May be somebody has an ideas.

Thanks a lot

Mike

Querying Access DB from MySQL (no replies)

Stacy Dixon — Mon, 09 Nov 2009 16:01:51 +0000

Hello,

I have an Access DB that I need to extract info from using MySQL. I assume thsi must be possible to do, but I'm just not sure how to do it. The thing is that I need to keep this Access db working and I have to use MySQL to extract this info. Does anyopne have any ideas? It would be greatly appreciated.

Thanks,

Stacy

A Guide for Migrating From Microsoft Access to MySQL (no replies)

Edwin DeSouza — Thu, 05 Nov 2009 19:11:10 +0000

A Guide for Migrating From Microsoft Access to MySQL
http://www.mysql.com/why-mysql/white-papers/mysql_wp_migrate-from-access.php

A Visual Guide to Microsoft Access Front-Ends with MySQL (no replies)

Edwin DeSouza — Thu, 05 Nov 2009 19:08:35 +0000

A Visual Guide to Microsoft Access Front-Ends with MySQL
http://www.mysql.com/why-mysql/white-papers/mysql_wp_access_visual_guide.php

Appending 440k records to MySQL through ODBC (no replies)

Harm Lommers — Thu, 05 Nov 2009 09:40:41 +0000

Hi!

I'm creating a script to merge a remote MySQL db with data from a local access backend. The problem occurs when I need to append a table with 440k records from Access to MySQL.

I removed all indexes except the PK from the MySQL table, but it fails halfway due to lack of free RAM.

I could move to a better pc, but I think there should be a better way. Exporting to a csv and import it in MySQL is an option, but that is trickier to include in a script (and the file is large too: 68MB!)

Can you give me some hints to get this done? Using 3.51 connector and 4.1.20.

Migrating from MSSql [BE] and MsAccess [FE] to MySQL [BE] and web-based [FE] (1 reply)

John Ortt — Sun, 08 Nov 2009 12:52:45 +0000

Hello everyone,

As stated above, I am currently developing an application using Microsoft Access and linking to an MsSQL server datastore. The time has come for a new refined version of the database starting from the ground up and I am keen to correct some of the issues inherent in access. I can write SQL and have learnt VBA through using Access, as well as a basic knowledge of html but I don't know any PHP. Is there a tool that would allow me to work in a wyswyg editor similar to access to create forms before uploading them to the server? I would like to do this for as little money as possible but I would be prepared to invest in software if it was worth it.

Thanks in advance,

John

Data from Access (8 replies)

Mickael Wenders — Tue, 17 Nov 2009 20:38:38 +0000

Hello,

I have a problem migrating data from an Access database to Mysql. I have a large database getting too large. Would you have an idea about migrating this data?

Thanks.

Can't see record through Microsoft Access using ODBC driver V5.1 (2 replies)

Peter S — Wed, 04 Nov 2009 18:44:31 +0000

I have been connecting to MySQL databases through Microsoft Access for quite a while but ran into a very strange issue. I am trying to access a users table and certain userID's can't be queried through Access.

I was able to access this user id (1134462) when writing a query in DBVisualizer but for some reason can't see it through Microsoft Access. I am running the latest ODBC driver V5.1. I noticed there are a myriad of flags in the setup, not sure some of those might help. Is there anyway this can be fixed. Any help would be greatly appreciated!

Norbert Paprocki - Access database with over 500k records be faster in MySQL (no replies)

Norbert Paprocki — Thu, 29 Oct 2009 02:43:32 +0000

Hi Folks,

Sorry if this is a stupid question.

I have an access database that we find is running very slowly for queries.
We had a local developer look at it and he said because it had so many records (a little over 500,000) that it would be best if we considered using MySQL and have Access as the front end.

I am curious if anyone else has done this and has the speed improvement been worth the conversion. We anticipate the database to grow at a rate of 50,000 records per year.

vba with mysql retrive data (no replies)

nag rajuu — Mon, 26 Oct 2009 17:03:17 +0000

hi

i am connecting to mysql database using VBA.

i am new to VBA i tried like this

Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rsc As ADODB.Recordset
Dim strsql as String

Set oConn = New ADODB.Connection
oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=tamp;" & _
"USER=root;" & _
"PASSWORD=test;" & _
"Option=3"
strsql = "SELECT * from info"
rs.Open strsql, oConn, adOpenStatic, adLockOptimistic

i got error upto this code

my intention is to display the info table data .

please guide me how to retrieve the data from info table.

any one guide me

Access DateDiff Equivalent (no replies)

Andy Hughes — Mon, 26 Oct 2009 14:58:39 +0000

Hi All....
I'm in the process of migrating our Access DB to MySQL 5 - I'm not sure if i'm doing this right, but i've been copying my Access Queries and creating 'Views' In MySQL. This seems to be working ok, but i'd like some confirmation on this.
Also, I'm getting an error on one of my queries. Please see code below:
I think it has to do with the DateDiff, but i'm not sure.
Thanks in advance
Andy
----
SELECT Vouchers.VoucherID, Vouchers.VoucherCode, Vouchers.StartDate, Vouchers.EndDate, Vouchers.Discount, Vouchers.VoucherTypeID
FROM Vouchers
WHERE (((DateDiff('d',[StartDate],Date()))>=0) AND ((DateDiff('d',[EndDate],Date()))<=0));

Finance assignment help, accounting homework help (no replies)

Sonu Kumar — Sun, 25 Oct 2009 09:43:39 +0000

Can’t do Accounting Homework?

Email me your accounting homework & accounting assignments & I will send you back the solutions. In addition to Accounting homework help & accounting assignments help, I also help in online accounting exams, online accounting tests & tutoring, accounting word problems, accounting case study & accounting essays.

Send me your accounting & finance assignments and i will send you back the answers. I also help in projects, papers and essays.

I also help in:-
(a) accounting homework help
(b) finance homework help
(c) managerial accounting assignment solutions
(d) net present value, future value & compounding
(e) bonds, stocks, options, derivatives homework
(f) financial management homework help
(g) statistics homework help
(h) homework ratio analysis & cash flow statement homework
(i) income statement & balance sheet & shares & debentures
(j) marginal costing, standard costing & variable costing
(k) marketing homework help
(l) economics homework help
(m) accounting-finance homework
(n) activity based costing , break even point & cvp analysis
(o) lifo, fifo, weighted average & journal entries homework & trial balance

please visit once http://www.financehomeworktutor.iwarp.com
or email me on financehomeworktutor@yahoo.co.in

accounting homework help, finance homework help, statistics homework help, economics homework help, financial accounting homework, finance help, financial management, accounting assignment help, accounting assignment answers, balance sheet homework, income statement homework, journal entries homework, present value, break even point homework, variable costing, absportion costing, standard costing, activity based costing, marketing homework help, ratio analysis homework , cash flow statement homework, cash budget, sales budget, lifo fifo, weighted average, wacc, macrs, bonds valuation, capm, project evaluation, capital budgeting homework, variance, working capital management, finance essays, finance projects & word problems, financial management homework

financial accounting homework help, finance ,homework, help, statistics, costing, accounting, marketing, economics, homework essays, probability homework, ANOVA, t-test, z-test, regression homework, correlation, multiple correlation, cost accounting, net present value, homework internal rate of return, journal entries, homework break even point, debt equity and preferred stock, bond valuation ytm, homework balance sheet, homework income statement, cash flow statement ffs, finance word problems, ratio analysis, homework inventory management, swaps, futures, contracts, options, exercise price, foreign exchange homework, accounting concepts standards gaap, portfolio management homework, corporate finance homework

please visit once http://www.financehomeworktutor.iwarp.com
or email me on financehomeworktutor@yahoo.co.in

Timeouts and Dtes (no replies)

Hansraj Pankhania — Thu, 22 Oct 2009 15:14:02 +0000

Hi All,

I'm a newbie to this forum, jointed after my client wanted to have access frontend to MySQL backend.

My job is to develop the access client that will reside on the PC, whilst another colleague will do the web interface .

Mu problem is that I keep encountering timeouts and every so often I get the message in Access :-

“ODBC –call failed. [MySQL][ODBC 5.1 Driver][mysqld-5.0.77]MySQL server has gone away (#2006)”

Then I lose connectivity.

I have tried all ideas from this forum, but no joy. Does anyone have a solutions this timeout.

Secondly, when I do have a connection, I seem to be have probems with dates. Do anyone know how to handle dates when linking to MySQL tables.

Regards

What's wrong with this Query? (1 reply)

Pedro Lami — Mon, 26 Oct 2009 12:53:41 +0000

DELETE
FROM test AS a
WHERE a.ID NOT IN
(SELECT MAX(b.ID)
FROM test AS b
GROUP BY b.ClientADSRef, b.ClientName, b.CreditorName, MAX(b.ID))

The inner select statement selects the appropriate records. All I want to do is delete the ones NOT IN this selection...

Data migration tool certification (1 reply)

af dabler — Fri, 23 Oct 2009 13:03:02 +0000

Hello,

I have been using the Talend software for a few months now and am very happy. I have seen the page on the Talend certification and the exam on the website ( http://www.talend.com/partners/index.php ).

I wonder what it takes to take the exam, if you need to know well Talend. Also is the exam available for everyone and is it free or paying?

Maybe someone would have an idea? Thank you.

Connector (no replies)

Prasanth Francis — Sat, 17 Oct 2009 06:08:06 +0000

Hi All

I am planning to set up a MS Access frontend + MySQL databse application. The app will be used by 250 users and probably around 20 concurrently.

1) Do I need to install the connector and set up the DSN on all the 250 machines whose users intend to use this app?

I am not technically sound so kindly giv eme your valuab;e suggestions and tips.

Regards
Franco

Access Front End Hassles (4 replies)

Attie de Beer — Thu, 12 Nov 2009 17:48:29 +0000

Hi All. I use MS Access as a client application to connect to MySQL thru ODBC. All works great - BUT: At times I get an error message in Access saying I and another user are changing the same record - even in a stand-alone situation. All ideas will be welcome.

Best Regards

A new open source ETL tool (1 reply)

af dabler — Fri, 09 Oct 2009 07:57:59 +0000

Hello,

We are trying to find a new open source ETL tool. Basically, we need our new software to perform tasks such as ETL, data migration and data synchronization.

For now, the program would not be used on large projects. There are a few open source tools on the market.

Any ideas? Thanks.

Access find query very slow. Looking for alternate solution (no replies)

carl R — Tue, 06 Oct 2009 18:24:46 +0000

I recently updated my Access v2.0 application to MS access 2007 and migrated the access v2.0 database to MySql. It is fully keyed (i.e. primary and secondary keys).

After the access migration I found access searches are very slow. reason:
1-the user clicks on a field of interest in a form.
2-then clicks control-F.
3-the access search function appears
4-The user enters the data of interest.
5-the OK button to initiate the search (query).

What I found is if the user does NOT uncheck the button "Search as formatted" on the "find" form, access will:
1- first do a select without a where clause and read ALL the records of the database into access.
2- Then access will search the resultant set for the data of interest.

The query can take minutes.

However, if the user DOES UNCHECK the "Search as formatted" button before clicking OK, access will create a select with a where clause. The search completes almost instaneously. Very fast.

I have searched the Microsoft web site and apparently the problem is the way the MS jet engine formulates SQL queries. MS suggests re-writing the find function.

I know I can write something to fix this, but I am hoping this is a problem that has already been solved and can be purchased a off the shelf solution. Perhaps a plugin for MS access 2007.

Can anyone suggest somthing?

Thanks
carl

Synchronization ? (5 replies)

Vesko Jl — Wed, 11 Nov 2009 13:33:37 +0000

Is there any way (besides dbsync access & mysql) to synchronize Access and MySQL. I need only one way sync, from access to mysql. Probably there is such thing in Access, or need to write some ado/vb code?

Any help will be appreciated! I'm tired of searching :)

My SQL Date field interpretation in Access (no replies)

John Hickey — Thu, 24 Sep 2009 13:50:55 +0000

I have a date field in my MySQL table that I am trying to edit in Access, but it is not letting me do it from the Access interface. I can CHANGE the date, but what I am really trying to do is DELETE the date, if one is entered.

In Access when I enter the date field and delete it (space bar it off, etc) and then try to move to the next record I get: "You tried to assign the null value to a variable that is not a variant data type."

In MySQL, it says the field is set up as such: 'date1` datetime NOT NULL default '0000-00-00 00:00:00'

When I try to enter 0000-00-00 00:00:00 on the Access side, (to overwrite a date I am trying to get rid of..) I receive "The value you entered isn't valid for this field." and again I can't save the change.

I need to be able to make the edits from the Access interface, what can I enter in the field that would be accepted?

Thanks!

Subform moves to a different Record (no replies)

Dale Howard — Wed, 23 Sep 2009 13:07:39 +0000

Hello,

I am currently upgrading a microsoft database to use a mysql backend. The whole database was working perfectly before the upgrade.

I have created a form with a subform within a subform. The main form shows the employees details PK = (empid). The subform shows the dates the person has worked PK = SPID. The sub-subform shows the minutes that person has worked on any given day for each of the sections the person has worked PK = SectionID.

Whenever i enter data on a row in the sub-sub form, on pressing enter to commit the row and move to the next row, the data is changing to show a different row. (ie a different row, to the one that has just been entered.) Oddly enough it does show the same row each time for all employees. I have checked that the child and master id's are set correctly for all subforms.

I have tried allsorts, even tried creating the form again.

I am using Access 2003, ODBC driver 5.1.

Please help.

Grant Create database permission to a user (1 reply)

P M — Wed, 23 Sep 2009 15:45:56 +0000

Hi,
I have created user as follows:
grant all on test.* to 'user1'@'localhost' identified by 'password'

after I login as 'user' , I am unable to create a database. I get the error message:
ERROR 1044 (42000): Access denied for user 'user1'@'localhost' to database 'dummy'

What is the problem? How can permissions be granted to this user to create databases.

I also tried to grant all on *.*, but this way the user can access all the databases that this user is not suppose to access. There is no way to REVOKE permission to a single database.

How to Grant Create database permission to a user?

Thanks in advance.

Data integration tool in Chinese? (1 reply)

af dabler — Fri, 25 Sep 2009 09:29:09 +0000

Hello to all,

Being in a pretty much international company, I have come here to ask a few things about ETL tools and their different languages.

We have offices in the US, in Europe (Italy, France) and in China. We think English is fine but our European team and Chinese team especially would like to get software in their language.

What we are trying to find is an ETL program to perform data integration, data synchronization and deduplication on our database in French, Italian and especially in Chinese.

Thanks for your help.

Access with MySQL (6 replies)

Rommel Escuadro — Wed, 23 Sep 2009 15:46:12 +0000

Good day!

I am very new to mysql. I have already started creating my database and tables in it but I would like to know about the following:

1. How can I embed mysql in access project so that when the user install the project it also installs the mysql server and the database with it.

2. How can I password protect the database.

3. Can I use Access to synchronize two database inside the server?

Kindly assist me. thank you!

Update a table by adding a new row with an expression (1 reply)

Nathan Spangler — Fri, 18 Sep 2009 15:00:17 +0000

This problem has me stumped. I have a table the records quality control metrics for a project team. The table stores technicians names and metrics associated with their work. Each tech can have multiple rows with their names and associated quality metrics. One of the columns stores the tech's hours per work area. I would like to create a new table that averages each tech's hours per work area. The new table would have each tech's name only one time and their avg hours per work area. I was able to do this with the following update query:

SELECT Technician, Avg(A1_COMPLETE.[HOURS PER AREA]) AS [Avg Of HOURS PER AREA] INTO [Average Hours Per Area]
FROM A1_COMPLETE
GROUP BY Technician;

The real value of this new table will be realized when I can display in a pivot chart each tech's avg hours per area against the overall avg hours per area (for the team as a whole). In the new table create above [Average Hours Per Area], I would like to add a new row using a query that inserts a new user called 'Average User' with the team's avg hours per area. This part I cannot figure out. Any ideas??? Can all of this be accomplished with one query from the A1_COMPLETE table???
Thanks for the help!

Problem with attaching files using access (no replies)

Mohammad Zachariah — Tue, 15 Sep 2009 19:54:23 +0000

Hi everybody,
I am using MSAccess as a front-end for data entry in MySQL database.
I faced a problem in attaching files using OLE object field the message is "there isn't enough memory to complete the automation object operation on the PDF object"
also another problem is i have to link it to execute it.
the other problem is the dumping of database . my Blob files are lost when I restore
any help please ?

Talend Open Studio and Integration Suite (1 reply)

Tommy Rs — Fri, 11 Sep 2009 09:45:17 +0000

Hi all,

I have been working with Talend Open Studio for a few weeks now and would like to have your opinion.

Is using Talend Integration Suite really better compared to using Talend Open Studio? On what kind of project can you not use Talend Open Studio and would be best to use Talend Integration Suite?

Thanks a lot for your answers.
Have a good day!