f



Import Columns from SQL Database

I am new to Access and have a quick question.  I'm trying to import
data from a SQL database, but the table that I'd like to import is
enormous.  I have gotten all of the connections to work, but as far as
I can tell, there's not a way to import certain rows of the SQL table
into a new table in Access.  Is it possible to somehow use a SELECT
statement rather than importing the entire table?

Thanks,
Ben
0
bwilde (27)
3/27/2008 1:48:18 AM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

2 Replies
616 Views

Similar Articles

[PageSpeed] 3

bwilde@gmail.com wrote:
> I am new to Access and have a quick question.  I'm trying to import
> data from a SQL database, but the table that I'd like to import is
> enormous.  I have gotten all of the connections to work, but as far as
> I can tell, there's not a way to import certain rows of the SQL table
> into a new table in Access.  Is it possible to somehow use a SELECT
> statement rather than importing the entire table?
>
> Thanks,
> Ben

Create a passthrough query to select what you want from the server table. 
Use that passthrough query as the input for an Append query to copy the data 
into a local table.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 


0
rickbrandt2 (2458)
3/27/2008 1:51:22 AM
On Mar 26, 9:48 pm, bwi...@gmail.com wrote:
> I am new to Access and have a quick question.  I'm trying to import
> data from a SQL database, but the table that I'd like to import is
> enormous.  I have gotten all of the connections to work, but as far as
> I can tell, there's not a way to import certain rows of the SQL table
> into a new table in Access.  Is it possible to somehow use a SELECT
> statement rather than importing the entire table?
>
> Thanks,
> Ben

Another way.

1. Create an ADP.
2. Connect to the SQL DB from the ADP (File -> Connections - or use
the dialog that will open when the ADP is created.)
3. Close the ADP.
4. Open or create the MDB where you want the new table to live.
5. Click File -> Get External Data  -> ... follow the prompts, select
the ADP, select the table. Click OK. That's it. Access now looks after
everything.
0
3/27/2008 2:27:46 AM
Reply:

Similar Artilces:

Database Database Database Database Software Cheap
Database Database Database Database Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). http://www.vehiclerepair.org/dbPro/dbpro.html ...

Database Database Database Database Software Cheap
Database Database Database Database Software Cheap Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). http://www.vehiclerepair.org/dbPro/dbpro.html ...

a database, is a database, is a err database
How many times can we see the same request from someone who wants to access data from a 'pick' database through what has come to be 'standard' practices (odbc, oledb) and still get the same old sloppy ' buy this proprietary utility (and above all, my services)' answer. I think most of these pick flavors should have some sort of layer (by now!) to handle this; If someone needs to do this, the service is really 'education' i.e to show them how. Lets cut the shit now and stop with this tired and silly BS and sad marketing schlock. Regards, -Jim Jim wrote: > How many times can we see the same request from someone who wants to > access data from a 'pick' database through what has come to be > 'standard' practices (odbc, oledb) > and still get the same old sloppy ' buy this proprietary utility (and > above all, my services)' answer. I think most of these pick flavors > should have some sort of layer (by now!) to handle this; If someone > needs to do this, the service is really 'education' i.e to show them > how. Lets cut the shit now and stop with this tired and silly BS and > sad marketing schlock. > > Regards, > > -Jim Jim who? I wonder? What is this? An attack on capitalism? Providing services for those who perhaps lack the time, skill, or knowledge to perform such tasks is hardly a crime. Maybe "standard" odbc and...

Import MS Office Excel Comma Separated Values Files into a MS Access 2003 database
Hi, I have been tasked with Importing MS Office Excel Comma Separated Values Files into a MS Access 2003 database. These files were exported from a DB2 database.I have also been given the DDL code. I have Googled a solution to this task and have tried creating these tables using the DDL code in a query within a blank MS Access database. Is there a more expedient way to achieve this? I noticed, for example, that a field in DB2 has a data type of "decimal" while the same field would have the data type of "number". Thanks, Zuf What follows is the DDL: -------...

MS Access database with a file extension of .mde
Good day: Can you kindly assist me in opening a MS Access database with a file extension of .mde? I get an error message stating "This database is in an unrecognized format". Any help is greatfull. TIA This usually indicates an attempt to open an Access 2000 database in an earlier version. -- MichKa [MS] NLS Collation/Locale/Keyboard Development Globalization Infrastructure and Font Technologies This posting is provided "AS IS" with no warranties, and confers no rights. "mary" <chloemelmom@yahoo.com> wrote in message news:a0a64947.0311151242.4df0c...

Importing Access databases into SQL Server
Hi there, I have a situation where an application needs to import data from number of access mdb files on a daily bases. The file names change every day. The data import is very straight forward: insert into sql_table select * from acess_table There are up to 8 tables in each access file and some access files will have less. So the process needs to figure out which tables exist in Access mdb file and import them whole into sql staging tables. Any recommendations are appreciated. Thanks It probably depends where you're running the load process from - that's not really clear (t...

Create MS/ACCESS database app for non-ms/access users
Is it possible to create an MS/ACCESS database application and package it for users who do not have MS/ACCESS loaded in their PCs? I was told that this is possible, but I don't know what software is needed. Do you know? If so, please answer this post. Thanks. SueB *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Per Sue Bricker: >Is it possible to create an MS/ACCESS database application and package >it for users who do not have MS/ACCESS loaded in their PCs? > >I was told that this is possible, but...

Importing data from an Excel spreadsheet into an MS Access database
I need to import data from a spreadsheet that is created new every day into an MS Access database. The data is in a workbook consisting of multiple work sheets. I plan to import using the TransferSpreadsheet method. The name of the spreadsheet will change every day, but the position of the data to be imported will always be the same. Here is my problem: Having never used this method before, I am unsure as to what to put in the Range parameter. The Range is (let's say) B26:L26 in a sheet labeled "TodayData". How would I put that in the Range parameter? Help in VBA only sh...

Adding records to a SQL Server database from MS Access
Thanks in advance for your help. I have just upsized an Access database to SQL Server with the hope of using a fairly extensive Access application that was part of it. But now, the forms will not allow the addition of new records (the >* button is greyed out). The forms use a macro to advance the dataset to a new record. How can I get the additions (inserts) turned back on? Regards. ...

Tutorial; Accessing MS SQL Database using C++!
hey! Im looking for a db tutoral which covers accessing a Microsoft SQL database on windows. I will be using VC++ if it matters. NO details just a basic introduction! Oracle DB access using C++ is also of interest! Thanks! -- Majk majk <msa_pub@softhome.net> wrote in message news:<BB7F5CCB.6D61%msa_pub@softhome.net>... > hey! > > Im looking for a db tutoral which covers accessing a Microsoft SQL database > on windows. I will be using VC++ if it matters. NO details just a basic > introduction! > > Oracle DB access using C++ is also of interest! > >...

Trouble when submitting sql-query to MS Access Database
Hi, I have some problems when trying to submit a simple insertion statement to a MS Access Database. The reading is fine, but when I want to execute an insertion statement using the following code: public void SQLCommand(String sql) throws SQLException { connection = DriverManager.getConnection(database); Statement stmt = connection.createStatement(); stmt.executeUpdate(sql); connection.close(); } gives the following error: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source) at org.strumpflohner.CodeNotebook.Data.DBManager.SQLCommand(DBManager.java:45) at org.strumpflohner.CodeNotebook.Data.TestClass.main(TestClass.java:15) Could someone help me? kito Suggestion 1: The error is caused because you're referring to a table that does not exist. Try to run the query in a seperate query editor and verify that it does work. Also check that the string you pass as argument is correct, e.g. not concatenation that has destroyed the intended string. Suggestion 2: You probably need to add quotes in the executeQuery. Whenever you pass a string to a database try to inclose the statement with...

Php and DataBase Abstraction for mySql, Sql Server, and MS Access?
Is there a good DataBase Abstraction layer that will handle the mySql, Sql Server, and MS Access engines? I do not wish to write the same functions with the three different PHP db functions. I see that php_dba is listed as an extension, but I determine which DBs are supported. However, I think that it does not support the ones I need. Thanks David wrote: > Is there a good DataBase Abstraction layer that will handle the > mySql, Sql Server, and MS Access engines? I do not wish to write the > same functions with the three different PHP db functions. > > I see that php_...

Re: Accessing MS SQL server or ORACLE database from the HP3K
Folks, One small clarification to my previous question: We are not trying to migrate our IMAGE data to another platform. We are trying to provide an enhancement where data that already exist in a database on another platform can be viewed, extracted and used to "complete" an existing entry in the IMAGE database, thus eliminating the need for the user to manually 're-enter' the data. Regards Paul Christidis =============================================================================== Folks, I was asked the following question today and decided to turn to y...

How to import a tab delimited text file into MS Access database
Hi, I have the following task to be done in VC++ 6.0. I have a tab delimited text file that needs to be imported into a MS Access table. The table is created and the table structure is defined using DAO methods. So now I have to fill this table with the data in the text files. For a few rows in the text file, the number of columns could be less than the number of columns in the table. Can anyone please post me the code to do this. Thanks a ton in advance. Pradeep wrote: > I have the following task to be done in VC++ 6.0. We'd be happy to give you the VBA code, but do you really want ...

how do you update a java.sql.date type in an ms access database
when i update a row in an access database the date field does not update to the correct date it brings up some funny date which i dont know where it comes from. Below is the method i use to update. maybe the date types are not compatable? thanks in advance, public static void bookIN(int custID, int rNumber,java.sql.Date bkInDate ){ try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String filename = "hotelDB.mdb"; String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="; database+= filename.trim() + ";DriverID=22;READONLY=true}"; Connection con = DriverManager.getConnection( database ,"",""); int yes = 1; Statement s = con.createStatement(); s.executeUpdate("update Rooms set CustomerId ="+custID+",BookINDate ="+ bkInDate +",Occupied = "+yes+" where RoomNo = "+rNumber+" "); s.close(); con.close(); } catch (Exception err) { System.out.println("ERROR: " + err); } } "chose" wrote... > when i update a row in an access database the date field > does not update to the correct date it brings up some > funny date which i dont know where it comes from. It comes from db's attempt to parse an invalid date-string... In Access/Jet,...

Database Database Database Software Cheap
Database Database Database Database Software Cheap Great Datase Software See Website Below. Ultra Easy to Learn (Typically 30 Seconds) Professional Programmable Database Ver. 2.3 2.1 Million Record Capacity, (New cond). Search Rate: 2000 / Records / Second. DataBase Type: Random Access. Can Create Unlimited Databases. Programmable fields for any Application. Has Six Seperate Field Sets All Programmable. Build Time One Second, (Auto Creates DB). Setup Time: Instantly, Just Enter DB Name. Ultra Cheap Price, Special $20, Paypal Accepted. Application Mailed Instantly (file Attached Email). http://www.vehiclerepair.org/dbPro/dbpro.html ...

SQL Server "Linked Server" to MS Access Database
I have a very frustrating problem. I have a situation that works fine on some computers, but not on others. I have SQL Server Express 2005 and Access 2003 loaded on 4 computers. I have set up a "Linked Server" in SQL Server linked to my Access database. I am running queries on t-SQL against the Access tables through the linked server. On all of the computers, I can do select queries and they all work great but what I need to do is run Update, Insert, Delete queries as well. On a couple of the computers, it works fine, locally and across the LAN, and a VPN as far as that goes. B...

Re: Accessing MS SQL server or ORACLE database from the HP3K #3
In a message dated 7/8/03 5:23:24 AM Pacific Daylight Time, pete@bookware3000.ca writes: > It's not MS SQL or Oracle but for what it's worth, we once had a Cobol > program > accessing a PostgreSQL database that resided on a Linux server by using the > C > libraries included with Mark Bixby's PostgreSQL port. > I for one would absolutely love to see an example of that! Sometime soon (?) I hope to be able to finally spend some time working with PostgreSQL on MPE and/or Linux. Having a known working example of how to access it via COBOL is a great ...

Re: Accessing MS SQL server or ORACLE database from the HP3K #6
On July 9, 2003 11:23 am, Duane Percox wrote: > * Performance was not that great so you wouldn't want to use this > for production, but proof of concept and for learning it is ok. Do you have any performance numbers for it all on Linux? Pete * To join/leave the list, search archives, change list settings, * * etc., please visit http://raven.utc.edu/archives/hp3000-l.html * ...

Re: Accessing MS SQL server or ORACLE database from the HP3K #3
Paul, if nothing else helps, you could use Java and JDBC with the JDBC driver of the respective target database. A small example showing access to an Image/SQL database can be found on the following web page (and can be adjusted to use an MSSQL or Oracle JDBC driver and database URL/logon fairly easily)... http://www.editcorp.com/Personal/Lars_Appel/JavaDemo/ -> JdbcProgram Calling Java from COBOL as a subprogram, however, is probably not the way that I would go, if exploring this road. I would probably let a program similar to the above run as a "gateway" in a sep...

MATLAB command to access MS Access database
Hi! Is there a MATLAB command which gives access to a .mdb MS Access database or do I need a special driver? Thanks, Andree Hi Andree Ellert, you can use activeXcontrol to interact with MS-Access database. access = actxserver('Access.Application'); returns the handle for Access. set(access, 'Visible', 1); will show the opened Access window. use get(access) and set(access) commands to find the methods available to call. -Vadivelu M =========== http://www.mathworks.com/access/helpdesk/help/toolbox/database/datatool.shtml "Andree Ellert" <ellert@gmx.net/////\\\\\> wrote in message news:eed9874.-1@webx.raydaftYaTP... > Hi! > > Is there a MATLAB command which gives access to a .mdb MS Access > database or do I need a special driver? > > Thanks, > > Andree Andree Ellert wrote: > > > Hi! > > Is there a MATLAB command which gives access to a .mdb MS Access > database or do I need a special driver? > > Thanks, > > Andree <http://www.mathworks.com/matlabcentral/fileexchange/loadFile.do?objectId=4045&objectType=file> -Hardik ...

Access MS-ACCESS database on server from client????
I m currently developing a java based application and want to know how can i make client access database located on the server? "HeMan_Speaks" <Lunar20092010@gmail.com> wrote in message news:d71ce567-0434-48b8-b1d2-bb191706ab45@w8g2000prd.googlegroups.com... >I m currently developing a java based application and want to know how > can i make client access database located on the server? I think you're going to have to expand a little bit more in your question here, as is not 100% quite clear what you're trying to do. You might want to make a little bit of a distinction between MS access, the development system that allows you to write code, build forms, and build reports, and that of the database engine that you choose to use with MS access. When you build an application MS access, you then choose your database engine, that might be oracle, SQL server, or perhaps more often leave the default jet database. So when you say make a client access application, are you talking about a piece of software that you plan to install and each computer? The fact that your introducing the issue of java further complicates your question. Perhaps your question is simply you have some java code running on a server, and with to open a mdb file (an access database file). In this situation you're not really using MS access at all here (you using what is called the jet database engine to read that mdb file). For all the trouble in this type of sce...

request for explanation on access an database in ms access
This is the code that I found on the internet for accessing an ms access database: import java.sql.*; class Test { public static void main(String[] args) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // set this to a MS Access DB you have on your machine String filename = "d:/java/mdbTEST.mdb"; String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="; database+= filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end // now we can get the connection from the DriverManager Connection con = DriverManager.getConnection( database ,"",""); } catch (Exception e) { System.out.println("Error: " + e); } } } What I don't understand is the declaration of database. What I did was putting the database in the same dir as the sourcecode and use "jdbc:odbc:CafeJolt.mdb". But this doesn't work. And the above code does, why is that??? Actually it's not Java question. It's ODBC question. You have to have ODBC connection. Depending of Windows it's usually something like this (for my Windows 2000): Start->Sttings->Control Panel->Administartive Tools->Data Sources(ODBC)-> User DSN -> [Add]-> Driver to Microsoft Access(*.mdb) === And here, finally you can choose file name. ...

Get the real type of columns from MS Access databases(OLEObject, text, memo, numeric)
Hello, I made one application that opens MS Access databases.And I have one problem.How can I find the real data type of columns(text, memo, OLEObject, numeric) like it is into Access.I tried with resultSetMetaData.getColumnType(columnIndex) but I receive java.sql.Types.I have two columns that contains OLEObjects(images).But I receive two different types for columns : VARCHAR once, and for second column SMALLINT.I only want to see the types like in Access(OLEObjects, Text, Numeric, Memo...).Thanks in advance! Best regards, Calin Pop ...

Web resources about - Import Columns from SQL Database - comp.databases.ms-access

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Fearing no punishment, Denver cops abuse crime databases for personal gain
(credit: Noel Hidalgo ) Denver police officers performed searches on state and federal criminal justice databases that were not work-related ...

Open source database improves protection and performance
Most enterprises rely on databases in some form or another, but they can be vulnerable to attack from people looking to steal information. They ...

Seattle’s Tableau Software snaps up database-computing startup in Germany
Seattle’s Tableau Software has acquired HyPer, a database-computing startup that spun out of research at a university in Munich, Germany. As ...

NTTC's Liquid Products Database making steady progress
NTTC's Liquid Products Database making steady progress Modern Bulk Transporter NTTC is asking carriers to suggest “proprietary blends” for ...

Amazon Web Services Announces that over 1,000 Databases Have Migrated to AWS since January 1, 2016
Amazon Web Services, Inc. (AWS), today announced that the AWS Database Migration Service is now generally available.

Taiwan launches database on areas vulnerable to quake damage
BEIJING (AP) — Earthquake-prone Taiwan is launching a database to inform residents which areas might be susceptible to creating potentially catastrophic ...

Interior Dept Spent $15 Million On A Crime Database That Doesn’t Work
Interior Dept Spent $15 Million On A Crime Database That Doesn’t Work

Microsoft's SQL database software now runs on Linux
Remember when Steve Ballmer likened Linux to cancer, and the notion of Microsoft courting the open source crowd was virtually unimaginable? The ...

Resources last updated: 3/20/2016 8:37:10 PM