From a non-Access to Access database

Using VB,is there any technique in which a non-MS Access database can be converted to a MS Access database.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi EXwithRaj:
See this PAQ for a similar situation: "Access Query In VB"

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
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.
EXwithRajAuthor Commented:
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?
>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:

with each table in database do
    sql_import="SELECT * FROM " + table_name
    sql_create="CREATE TABLE " + table_name + "( "
    with each column in result_set
        sql_create=sql_create+column_name+" "
    end with
    sql_create=sql_create+"PRIMARY_KEY" <- this depending of your needs
end with

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
EXwithRajAuthor Commented:
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?
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.

EXwithRajAuthor Commented:
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!
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

EXwithRajAuthor Commented:
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.
good bye
EXwithRajAuthor Commented:
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.
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.

EXwithRajAuthor Commented:
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!
EXwithRajAuthor Commented:
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.
marcin79 if you are still around you should check out this thread:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.