Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

From a non-Access to Access database

Posted on 2004-11-26
18
Medium Priority
?
244 Views
Last Modified: 2010-04-17
Using VB,is there any technique in which a non-MS Access database can be converted to a MS Access database.
0
Comment
Question by:EXwithRaj
18 Comments
 
LVL 27

Expert Comment

by:Dabas
ID: 12684881
Hi EXwithRaj:
See this PAQ for a similar situation:
http://www.experts-exchange.com/Q_21068336.html "Access Query In VB"


Dabas
0
 
LVL 1

Accepted Solution

by:
silaupura earned 200 total points
ID: 12684970
Method 1:
open the database in excess in which u want to import tables of destination database.

File --> Get External Data --> Import

Method 2:
U can do this other way round ie
export data from the database u are using right now into Excess format.

Method 3:
If u are using Sql server or If u have SQL Server Installed then u can use Data transfer services of sql server also.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 12685023
do you have a specific Non access db in mind?

is this a one-off or regular requirement?

what are you actually requiring a solution to duplicate the Database schema from your source to a new access database..
or are we just talking about importing to a few tables?

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:marcin79
ID: 12688267
IMHO the simplest way to do such task via vb would be to use ODBC drivers, BUT you have to know exact structure of database you importing.
Through the ODBC drivers you could do a series of "SELECT *"'s one for each table, and during analyse of a resultset You could do an INSERT to a new database.
I would use this aproach becouse it is the most flexible and platform independent. In this way you could import for example excel sheets (or any other data source which has a odbc driver) to any database (which of course has an odbc driver :) ).

hope this helps
0
 
LVL 4

Expert Comment

by:abbdan
ID: 12707653
Most databases will let you export data in text format.  If this is the case for your "non-access" database then you can import the delimited text file into your Access database using file/import.

Hope this helps.
0
 

Author Comment

by:EXwithRaj
ID: 12715032
marcin79,what is this IMHO?Besides,isn't it also possible to use OLE DB providers,instead of ODBC,to perform the same task?
    You've said:"You could do an INSERT to a new database.".But this new db must have exactly the same design as the db you're importing from.Therefore,how do we know the structure of the old db,in the 1st place?
0
 
LVL 2

Expert Comment

by:marcin79
ID: 12719018
>Therefore,how do we know the structure of the old db,in the 1st place?
that was what i noticed first "BUT you have to know exact structure of database you importing."
IMHO means In My Humble Opinion
to get the structure of a database you inporting could do a "select *" on evary table, depending on the result create tables in new database and run the actual import.
As for ODBC and OLE DB don't know almost anything on the others. I use ODBC drivers becouse they are much more portable (ie. different platforms) and they do exactly what i need. But to answer your question I think that it is possible to use OLE DB.
this might be a pseudo code for checking table structure:

open_db_import
open_db_export
with each table in database do
    sql_import="SELECT * FROM " + table_name
    sql_create="CREATE TABLE " + table_name + "( "
    run_sql_import()
    with each column in result_set
        sql_create=sql_create+column_name+" "
        sql_create=sql_create+check_column_type(data_from_selected_column)+","
    end with
    sql_create=sql_create+"PRIMARY_KEY" <- this depending of your needs
    sql_create=sql_create+")"
    run_sql_create()
end with
close_db_import
close_db_export

function check_column_type should return a string describing data type (ie int,varchar,blob...)

this might require fine tuning but if you will do it correctly you will receive a program for importing every database into any kind of database, and quite good describes what i'm thinking about

Hope this helps
Marcin
0
 

Author Comment

by:EXwithRaj
ID: 12723672
marcin79,why do you've to do " "select *" on evary(sic) table"  because this will return all the records.If there're 50,000 records it'll return that many no. of records.Isn't this crazy?All that you've to do is return just 1 record.Based on the resulting resultset you can check-out the field-types & names,to create a new table in another db,with the same table structure.Isn't this what you've in mind?
0
 
LVL 2

Expert Comment

by:marcin79
ID: 12723718
yeah but how you will know how to limit it by the where clause, if you don't know anything about the db ? I've just assumed that this might be the tool for db converting without having any knowledge about db.
Yes select * selects all records in table, but "*" is needed to get every columns from db you not know anything about, but you don't really process whole result set only 1-st row. You may limit the result with some tricks I don't know of course :)  I'm not a guy which will pretend to know anything.

marcin
0
 

Author Comment

by:EXwithRaj
ID: 12761102
marcin 79,the reason you keep harping on the "SELECT *" is obviously your ignorance of how to return just 1 row.And this ignorance can prove fatal in a serious development environment.Here's how to return just 1 row sans the WHERE clause:
SELECT top 1 *  <table>.Simple!
      Hope this comes as as a big relief to you!
0
 
LVL 2

Expert Comment

by:marcin79
ID: 12766387
as i told before i cannot know everything (mistyped - anything) sorry
about ignorance its not ignorance, i've accidently hit this thread and noticed this question and thought that i might be able to help you.
The point you keep digging it's not very important the (IMHO) most important part was the method to replicate the database - and i've proposed you a working solution - regarding of the method of fetching the first row. You said NOTHING about serious development environment in your question, and either the language you are using (VB) and the database engine  (ACCESS) does NOT point to serious development enivronment. You even didn't said what type of database you want to import (will it be one type or many types).

and at last
SELECT top 1 *  <table>.Simple!

NO NOT SIMPLE your select instruction will NOT work on MySQL and SapDB (tommorow will check the oracle) databases (which are non-access databases too)
on mysql it should be
select * from <table> limit 1
and SapDB
select * from <table> where ROWNO < 2

I think too that You sholdn't call enybody ignorant where You are not 100% sure that You are right. As you see above theese statements are different for different databases so you can't use one for all and becouse non-access is very wide I CANNOT give you the simple and universal soution.
and no it is not a big relief to me.

and in this light i can say YES I AM ignorant becouse i don't know everything about everything

marcin
0
 

Author Comment

by:EXwithRaj
ID: 12772565
marcin79,thank you for your help.But your last reaction is indeed laughable & naive.
You kept on harping on "SELECT *" which is wrong,& I'd to correct you my showing how to retrieve just 1 row sans using WHERE clause.That my solution will not work on MySQL and SapDB,is non of my problem.If it doesn't,use equivalent query compatible with the two.The main point is you shouldn't use something like "SELECT * FROM <table>".It's disastrous!
      Your view that "You said NOTHING about serious development environment in your question" is indeed ridiculous!What do you think I am(or we all are) doing in Experts-Exchange - playing hockey or cricket?More strangely,your assertion that "the language you are using (VB) and the database engine  (ACCESS) does NOT point to serious development enivronment" is pathethic?What's you definition of "serious development enivronment",in the 1st place?Using MySQL and SapDB only - & not Ms Access?On one hand,you made a mistake with the "SELECT *" solution & then you pass a bogus comment on the seriousness of VB(the world most popular RAD).I find your reactions inexplicable!
      Yes,my "a big relief to you" comment is well-placed & you deserve it.If you knew that just 1 row could be retrieved - sans using WHERE clause - then should've mentioned that.Your
not doing so made me use the word "ignorant".
      Besides,your English is awful.It was real pain reading thru your badly constructed sentences.
0
 
LVL 2

Expert Comment

by:marcin79
ID: 12774318
good bye
0
 

Author Comment

by:EXwithRaj
ID: 12780285
marcin79,goodbye to you too.But you made a furthur joke of yourself by "chickening out" without responding to my rejoinder.Were you unable to fathom the words in my response?
    It doesn't behoves a joker like you to pass judgement on "serious development enivronment".That you questioned VB  and Access as "serious development enivronment" is one of the highlights of your jocular proclivities.
     I advise you to go and pick on someone of your own kind.That my words had the right impact on you is all too obvious.
0
 
LVL 2

Expert Comment

by:marcin79
ID: 12781615
As far as i know the ee is not for fightin' each other but for exchange of knowledge. I wrote before that i don't know everything, but you still trying to prove me that i'm ignorant - BUT i gave you working solution (as a pseudocode), so you received help from a ignorant. You didn't liked this answer so i've said good bye.
The question you asked was not clean. You asked for non-access 2 access databse but when i told you about sapdb, mysql it was not what you asked for. Sorry, I can't read your mind. I've read through your other questions and this is not your first time when you are offending the guys which are trying to help you. If you know everything about everything and all the guys out there are ignorants it is you who should be an expert.
About the tools:
Access have a quite good database engine - nothing to say here
VB - slow, ugly syntax language with only partial support of object programming and forcing developer to use additional dependancies (.NET/VB runtime). I personally think that it is not very cross-platform too (btw try to write/build custom control in pure VB)

Sorry will not try to fight anybody on this forum.

Regards
Marcin
0
 

Author Comment

by:EXwithRaj
ID: 12791587
marcin79,you've misread me.Your "for fightin' each other" comment is misplaced.My reactions are influenced merely by technical know-how.My reactions are purely matter-of-fact with no intention of sounding haughty.When I used the word "ignorant" it was not to assert my superiority at all.It's not as if I  know everything;otherwise,why would I be posting queries after queries for the last 2 years or so.It was influenced by the "SELECT * FROM" advise.The word "ignorant" is just a "technical assertion" & not assertion of my pride.
           Of course,I must admit ,apart from this,there's nothing wrong with the pseudo-code. The gist of what you'd provided as solution is appreciated by me.
                 Besides your branding VB as not a "serious development enivronment" is a sure put-off.Every major high-level language has it's own deficiencies,in some way or the other.You may not like one because of some specific reason.But to make such a sweeping allegation against VB is uncalled for.This comment of yours,"I personally think that it is not very cross-platform too (btw try to write/build custom control in pure VB)",is confusing.Because the ActiveX controls created in VB are binary components,which can be used in other high-languages.
      I am very,very surprised that with your comment that I am "offending the guys which are trying to help you. ".What prove do you've?Show me!
0
 

Author Comment

by:EXwithRaj
ID: 12865741
marcin79,I hope you've realised by now that your reactions were totally bogus & out-of-place!Your sequemish responce to my replies are,indeed,pathethic.Besides,I advise you not to make false allegation against someone & remain quite.
  The false sense of hurt you'd exihibited would've been avoided had you simply understood the spirit & gist of my responses.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 14296936
marcin79 if you are still around you should check out this thread:

http://www.experts-exchange.com/Miscellaneous/Lounge/Q_21469989.html
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Six Sigma Control Plans
Progress

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question