473,322 Members | 1,496 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

SQL dbase to Access

10
hello all,
i'm new in access and i need some help. here's the scenario, i need to copy a table in sql server to access. then edit the copied table and update the table in the sql server of any changes made from the table. the sql server is a third party so i cant do anything much from there. and i need to do this using access macro.
what im doing now is copy the table from sql server to ms excel then to access. is there any other way to do this using macro? any help is greatly appreciated.
thanks!
Dec 14 '06 #1
7 1922
willakawill
1,646 1GB
hello all,
i'm new in access and i need some help. here's the scenario, i need to copy a table in sql server to access. then edit the copied table and update the table in the sql server of any changes made from the table. the sql server is a third party so i cant do anything much from there. and i need to do this using access macro.
what im doing now is copy the table from sql server to ms excel then to access. is there any other way to do this using macro? any help is greatly appreciated.
thanks!
Hi
You can link to the sql table from access or import to an access table from sql server.
Dec 14 '06 #2
MMcCarthy
14,534 Expert Mod 8TB
Hi
You can link to the sql table from access or import to an access table from sql server.
BTW you will need an odbc or DSN connection to the SQL Server to do this.

When the Link or Import wizard opens scroll down the file types until you select odbc types and the odbc wizard will open.

Mary
Dec 14 '06 #3
NeoPa
32,556 Expert Mod 16PB
If you need more help (with updating the data in Access or uploading the data back to SQL Server) then you need to make your question clearer.

MODERATOR
Dec 14 '06 #4
rytsyd
10
thanks! i already linked the table from sql to access. its working fine! but what if i need to make a local copy of the linked table. i tried the copyobject from macro, it has been copied successfully but it is still linked. how can i copy the linked table and make it a local copy of access? (the table will be edited and be used as a reporting tool but as of now, updating of sql server is a no go so i need to make a local copy)

thanks again!

p.s. need it to be done in macro.
Dec 15 '06 #5
MMcCarthy
14,534 Expert Mod 8TB
thanks! i already linked the table from sql to access. its working fine! but what if i need to make a local copy of the linked table. i tried the copyobject from macro, it has been copied successfully but it is still linked. how can i copy the linked table and make it a local copy of access? (the table will be edited and be used as a reporting tool but as of now, updating of sql server is a no go so i need to make a local copy)

thanks again!

p.s. need it to be done in macro.
The easiest way is to use the Access query window and set the query to a make table query. Simply create a new table based on the link table. This will create a local access version of the table. If you need it in a macro simple run the query in the macro.

Mary
Dec 15 '06 #6
rytsyd
10
Thanks a lot Mary! it works great!
Dec 15 '06 #7
MMcCarthy
14,534 Expert Mod 8TB
Thanks a lot Mary! it works great!
You're welcome.
Dec 15 '06 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Ivan | last post by:
Hi, SQL Server 2000 SP3 Windos 2000 Server SP4 I have a DTS package that imports data from a dBase IV databse with files located in two folders (dBF1 and dBF2). I use a transform data task...
8
by: chippy | last post by:
Hi, I've a VB script that creates a Access object in a word doc. Here is the full script. It works for all but the Export. Which always fails with a 3011 error. If I do the same in Access as a...
1
by: Derek Griffiths | last post by:
Most of the complicated programs I write are boring applications that manipulate databases associated with the medical billing program where I work. The program uses foxpro dbases (version 3) for...
6
by: Tomek | last post by:
Hi, Please help me with Data Connections into dBase files. I have catalog with lot of files like *.dbf and *.mdx . Every file contains one table (I can see it in MS Access). How can I make...
4
by: JeffP | last post by:
datagrid1 has a single column contact I'm getting an invalid datasource at... Me.DataGrid1.DataSource = cmd I don't want to use the methods here, I usually use SQLHelper psuedo code: dim ds...
2
by: chiefsitebuilder | last post by:
I have a question about saving an MS Access program to CD and verifyng that the program works after being saved. Here is my situation, I have a person who developed an application for me using...
0
by: j.np.12 | last post by:
I have an Access XP database which links to dBase III format tables and updates them. The app used to work fine until recently when the dBase index tables keep getting corrupted and need to be...
2
by: Songyang | last post by:
I'm new to this group. In my company in Japan, we have been using dbase for almost 20 years, and we are now switching to foxpro. Dose anybody here has experience in converting dbase programs from...
6
by: Aleyna[] | last post by:
Hi all, I wanted to connect an old DBASE file (I don't know the exact version) but I could not make it at all. I couldn't find a way to execute sql statements on db file using c#. The...
1
by: manontheedge | last post by:
I'm importing some .dbf files into Access, but getting "can't recognize format errors" on some of them I realised that the ones that transfer okay are "dBASE III/IV/Clipper" file format, and the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.