SQL Insert from Access

I have a query in access 2003 containing new patients that needs to be 
inserted into a SQL server 2005 table. This will be done once a month.

ImportData contains the connection string.

Can anyone finish the syntax or tell me a better way of doing this.

Thanks in advance.

Kurt

===============================================================================================

Set cnn = New ADODB.Connection

cnn.Open ImportData

 strSQL = "INSERT INTO dbo.tblPatients (PFirstName, PLastName, 
MedicalRecNumber,DOB,MRSNID,EmployeeID)"

strSQL = strSQL & "SELECT FirstName, LastName, 
MedicalRecNumber,DOB,MRSNID,EmployeeID"

strSQL = strSQL & "FROM qryUniqueNewPatients"

cnn.Execute strSQL

===============================================================================================


0
Dataman
12/17/2009 5:42:34 PM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
676 Views

Similar Articles

[PageSpeed] 50

Have you tried the query?  What is the problem?  The only thing I can thing
of is that there is no space between EmployeeID)" and "SELECT, and then
again between EmployeeID" and "FROM.  When building SQL strings, it's always
useful to debug your output and see what it really looks like (debug.print,
I'd guess, though I am not sure if this is VB, VB.net, VBScript, VBA, etc).
Some of the mistakes are much easier to spot when you see the output.






On 12/17/09 12:42 PM, in article
QI2dnZc6w5IQ8LfWnZ2dnUVZ_jednZ2d@bresnan.com, "Dataman" <kurt@dataman.cc>
wrote:

> I have a query in access 2003 containing new patients that needs to be
> inserted into a SQL server 2005 table. This will be done once a month.
> 
> ImportData contains the connection string.
> 
> Can anyone finish the syntax or tell me a better way of doing this.
> 
> Thanks in advance.
> 
> Kurt
> 
> ==============================================================================
> =================
> 
> Set cnn = New ADODB.Connection
> 
> cnn.Open ImportData
> 
>  strSQL = "INSERT INTO dbo.tblPatients (PFirstName, PLastName,
> MedicalRecNumber,DOB,MRSNID,EmployeeID)"
> 
> strSQL = strSQL & "SELECT FirstName, LastName,
> MedicalRecNumber,DOB,MRSNID,EmployeeID"
> 
> strSQL = strSQL & "FROM qryUniqueNewPatients"
> 
> cnn.Execute strSQL
> 
> ==============================================================================
> =================
> 
> 

0
Aaron
12/17/2009 7:29:06 PM
"Dataman" <kurt@dataman.cc> wrote in message 
news:QI2dnZc6w5IQ8LfWnZ2dnUVZ_jednZ2d@bresnan.com...
> I have a query in access 2003 containing new patients that needs to be 
> inserted into a SQL server 2005 table. This will be done once a month.
>
> ImportData contains the connection string.
>
> Can anyone finish the syntax or tell me a better way of doing this.
>
> Thanks in advance.
>
> Kurt
>
> ===============================================================================================
>
> Set cnn = New ADODB.Connection
>
> cnn.Open ImportData
>
> strSQL = "INSERT INTO dbo.tblPatients (PFirstName, PLastName, 
> MedicalRecNumber,DOB,MRSNID,EmployeeID)"
>
> strSQL = strSQL & "SELECT FirstName, LastName, 
> MedicalRecNumber,DOB,MRSNID,EmployeeID"
>
> strSQL = strSQL & "FROM qryUniqueNewPatients"
>
> cnn.Execute strSQL
>
>

As qryUniqueNewPatients is in your Access database, you will need to find a 
way to link that data to SQL Server. Check BOL for information about linked 
servers.

Or you could work the other way around - created a linked table in Access to 
your SQL Server table, and have your qryUniqueNewPatients insert the 
relevant data into that linked table.

-- 
Dan 

0
Dan
12/18/2009 1:00:18 PM
As Aaron suggested, try the query and make sure that it is valid
T-SQL. One way to do this is to put a debug.print statement
immediately after you finalize the strSQL string, then copy the output
from the Immediate window into a SQL Server Management Studio query
window and execute it. I'd work with a test database, not live
production database since you are modifying data -- you could get the
syntax correct, but still do something horribly wrong, such as omit a
necessary WHERE clause :)

--Mary

On Thu, 17 Dec 2009 10:42:34 -0700, "Dataman" <kurt@dataman.cc> wrote:

>I have a query in access 2003 containing new patients that needs to be 
>inserted into a SQL server 2005 table. This will be done once a month.
>
>ImportData contains the connection string.
>
>Can anyone finish the syntax or tell me a better way of doing this.
>
>Thanks in advance.
>
>Kurt
>
>===============================================================================================
>
>Set cnn = New ADODB.Connection
>
>cnn.Open ImportData
>
> strSQL = "INSERT INTO dbo.tblPatients (PFirstName, PLastName, 
>MedicalRecNumber,DOB,MRSNID,EmployeeID)"
>
>strSQL = strSQL & "SELECT FirstName, LastName, 
>MedicalRecNumber,DOB,MRSNID,EmployeeID"
>
>strSQL = strSQL & "FROM qryUniqueNewPatients"
>
>cnn.Execute strSQL
>
>===============================================================================================
>
0
Mary
12/19/2009 6:00:14 PM
Reply:

Similar Artilces:

Logging Outlook Web Access
Hi, I'm new at this. How can I tell as an Administrator how many times someone logs on and off of Microsoft Outlook Web Access? Does that information get logged somehow? Thanks in Advance, John Hi - OWA is part of Exchange, not Outlook, so this would be best posted in an Exchange group. This is a server issue - auditing can be enabled to log login/logout events. John wrote: > Hi, > > I'm new at this. How can I tell as an Administrator how many times > someone logs on and off of Microsoft Outlook Web Access? Does that > information get logged somehow? > >...

Access
�Como se establecen las relaciones en access? ...

unable to access OWA
Hi, We have a problem with OWA. We are using OWA for quite a time now and everything worked fine. Until this week, with no apparent reason we are unable to access the OWA-website. When going to the OWA-website, the page seems to load and you see the different buttons come up, but then it says "Loading..." and it stays there for the rest of time. Browsing the different forums and newsgroups haven't helpt us out yet. So any help or hint would be welcome. Config. Windows 2000 server with all service packs, exchange 2000 SP3 and the clients are Windows XP (some with and some with...

Accessing the schema type value from XSLT ...
Folks, Have any of you figured out a way in .NET 1.1 to access the scheme type of a Xml node from XSLT? Appreciate any pointers or code snippets that you can provide. After some extensive research (& head-scratching) I understand I need the PSVI(Post Schema Validated Infoset). I can't seem to figure out how to use PSVI with XSLT. Am I on the right track here? Thanks, -Naraendira -- NaraendiraKumar R. R. wrote: > Have any of you figured out a way in .NET 1.1 to access the scheme type of a > Xml node from XSLT? Appreciate any pointers or code snippets that you can >...

Exporting specific cells then import to access
I have a folder of about 500 excel files. I dont care how many steps it takes to have this done. BUT is it possible to export specific cells out of excel and then import them into access. For example cell b5 = $##,### in all my excel documents. I need to export that specific number in that specific cell. Then I need to insert just those numbers into a table in access. Thanks for the help in advance, Matt >is it possible to export specific cells >out of excel and then import them into access Yes, it is possible.You have to loop on all excel files in that folder, open it one by one,...

PDF file in Access Runtime 2007
I am trying to save a report as a pdf file in Access 2007. I have previewed the report and clicked pdf or xps. When using the full version of Access it works fine. But when I am running this in a runtime version I get the error "can't save the output data to the file you've selected" The file is not open, I am not using a templete, I have enough free space and I have permisions to write to the folder it is saving. This also happens if I try to email the report. Any Idea? Take a look at this article: http://office.microsoft.com/en-us/access/HP051889881033.aspx -- ...

.msg file access violation in Outlook
Hi, I have a .msg file that was created using Outlook 2000 SP-3. I am able to open the file only once in every boot cycle. The rest of the time I get a shariung violation (when moving/deleting) or an access violation message when re-opening in Outlook. How do I solve this? Thanks. -- AKS "dee" wrote: > Hi, > > I have a .msg file that was created using Outlook 2000 SP-3. > > I am able to open the file only once in every boot cycle. The rest of > the time I get a shariung violation (when moving/deleting) or an access > violation message when re-op...

Mail Merge from Access with datasource being a password protected
I have an Access application distributed to users of Office 2003 and Office 2007 via a runtime front end. The application needs to trigger a Mail Merge which will use query results in back end, which is protected by a password. I have coded in VB to cope with this in an Office 2007 environment - but when running with Office 2003 Word is asking for the data source when initially opening the mail merge document from Accesss; when opened directly it does not ask for the data source and when responding with "No" to the SQL prompt it opens fine. Can anyone help? My code...

Reading a hyperlink from a table and inserting it into the hyperlink property...
I would like to know how to read a hyperlink from a table and then in turn insert that hyperlink into the hyperlink property of a command button to execute it. My database has serveral hyperlinks stored in the fields of a table. Thank you in advance Max strHyperlink = HyperlinkPart(Trim(Nz(Me!lnkDescription.Value, "")), acAddress) strLinkName = HyperlinkPart(Trim(Nz(Me!lnkDescription.Value, "")), acDisplayText) int_PPT_Pg_Nbr = HyperlinkPart(Trim(Nz(Me!lnkDescription.Value, "")), acSubAddress) str_ToolTip_PPT = HyperlinkPart(Trim(Nz(Me!l...

insert>video option is grayed out in frontpage 2003
the option is grayed out on the menu. Take a look at this article: How to insert a video in FrontPage 2003 http://support.microsoft.com/kb/825508/en-us -- ~ Kathleen Anderson Microsoft MVP - FrontPage Spider Web Woman Designs http://www.spiderwebwoman.com/resources/ Please reply to the newsgroup for the benefit of others "pagewest" wrote: > the option is grayed out on the menu. ...

CRM SQL Database size, scaling and archiving
Our company is considering Dynamics CRM. Us poor IT guys have to make sure it keeps running for years to come. And we lack the experience, including bigger SQL implementations. I'm concerned about 3 things. They might be dumb questions, but I'm asking anyway. What's the maximum database size in terms of relations/contacts ? At what stage does SQL grind to a halt. Are we talking 100.000 contacts ? Or 1.000.000 ? or 10.000.000 ? Can I seperate the different datasets (email-history, cases, documents etc) across seperate SQL databases or even servers ? For instance, if someone ...

Using Access 2003 for Inventory
I plan to setup our inventory system in the warehouse using Access 2003. Now, what I would like it to do (and it has been a long time since I have tried to do anything with access so I don't remember if this is possible or not) is automatically create a report that will tell me when I am low on equipment and possibly e-mail it to me and my supervisor. I'd also like to do the samething so with our drop material (cables, ground blocks, etc.) and have it e-mail to the owner so that he can place an order. The system, ideally, will also allow me to view what our technicians have on ...

CRM 3.0: Unable to access CRM from the same machine
I've installed CRM 3.0 on a server with four IP addresses (one for the machine and one for each Web server hosted on the machine). The DNS server maps "crm" A record to the right IP address. >From clients, I can access the CRM server using http://crm/ >From the server, I can only access CRM web server using the IP address. If I use the http://crm/ URL I always get the authentication dialog box and authentication doesn't work. Any advise? Thanks, Andrea You may need to explicitly add the server alis to the intranet Zone sites list. -- Matt Parks MVP - Microsof...

Error Installing CRM on Windows Server 2008, SQL Server 2008?
Anyone have any answers on this for me? I have seen a few posts which relate to SQL Server 2003 but not 2008? Here is the error I am receiving. --- Setup failed to validate specified Reporting Services Report Server http://SERVER2008/CRMReportServer. Error: Client found response content type of 'text/html; charset=3Dutf-8', but expected 'text/xml'. The request failed with the error message: -- <HTML dir=3D"ltr"> <HEAD><meta name=3D"GENERATOR" content=3D"Microsoft SharePoint" /><meta name=3D"progid" content=3D"...

how to insert control's in CTabCtrl
hi group, i have created a CTabCtrl on my dockable window with the following code int CDocDebug::OnCreate(LPCREATESTRUCT lpCreateStruct) { if (CSizingDocBar::OnCreate(lpCreateStruct) == -1) return -1; SetSCBStyle(GetSCBStyle() | SCBS_SHOWEDGES | SCBS_SIZECHILD); ///CTabCtrl Creation CRect rect; GetClientRect(&rect); m_imageList.Create(IDB_TREEVIEWBITMAP,16,1,RGB(255,255,255)); m_wndTabCtl.Create(TCS_BOTTOM|TCS_FORCELABELLEFT |WS_CHILD | WS_VISIBLE |WS_TABSTOP, rect,this,12345); m_wndTabCtl.SetImageList(&m_imageList); DWORD dwExStyle= m_wndTabCtl.GetExtendedStyle(); ...

Error sending emails from Access through Outlook
I am having the typical problem when sending an email from Access. I get that error saying that another application is trying to send an email on my behalf or whatever. I heard that it can be bypassed by using sendObject, is this true? If so, how would i modify my code to use this.Function Email()'Set reference to OutlookOn Error GoTo Errhandler'Dim strBCC As StringDim db As DAO.DatabaseDim rst As DAO.RecordsetDim objOutl As Outlook.Application'Dim objEml As Outlook.MailItemDim i As IntegerSet db = CurrentDbSet rst = db.OpenRecordset("qryEmailReminder", dbOpenSnapshot)...

Syncronization of Access Database with Replica over the Net
Dear Professional. I want to Sycronize a database and its replica using the Net. Can you guide me in this regard. Best Wishes, Noor Mohammed Khan Try looking at the information here: http://www.dfenton.com/DFA/Replication/index.php?title=Main_Page -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com If my post was helpful to you, please rate the post. __________________________________ "eJirga@gmail.com" wrote: > Dear Professional. I want to Sycronize a database and its replica > using the Net. Can you guide me i...

MSFlexGrig and SQL Query
How can i load dates in a MSFlexGrid Object whit a SQL Query???? ...

Can't Reply or Forward Original Msg in Outlook Web Access
When using Outlook web access to get my emails, when forwarding and replying to emails, I lose the orginal email so that when I reply or forward there's nothing there. I am using Internet Explorer Ver. 6. Does anyone know how to fix this? Thank you. ...

Optimizer on SQL 2005
I ran dbcc updateusage ('demodb'). I than ran dbcc show_statistics ('location', 'ix_phone'). The update date (statistics update date) remained old. Why? Why weren't statistics updated? BTW.. I have auto update statistics set to on. Thanks in advance DBCC UPDATEUSAGE doesn't update statistics. It only updates the info SQL Server keeps about number of rows, pages and such. To update statistics you use the UPDATE STATISTICS command. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blog...

Setup failes on SQL server
After attempting to install CRM 1.2 using the MSDN version I keep getting a "Microsoft CRM Setup could not validate this instance of SQL server" error. I have attached the install log. PLEASE HELP!!!!! I cannot get past this screen System config: Windows 2000, SP4 SQL 2000 SP3a MSMQ Domain Controller Logged in under admin account [17:04:08] ===========> Starting Microsoft CRM Server setup: 12/15/2004 17:4:8 Build: 1.2.3297.0 [17:04:08] Command Line /L*V c:\crm\install.log [17:04:08] Found Platform Major Version 5, Minor Version 0 [17:04:08] FIsWin2kRemoteSessionMappe...

OT: Accessing this NG via Usenet-News
Well, how lovely to discover that I've still got 5.7gb's left to use with Usenet-News, from a block that I bought year and years ago ! :-) I've now deleted msnews.microsoft.com from OE ....now lets' have a rummage..... regards, Richard From: "RJK" <nosuch@hotmail.com> | Well, how lovely to discover that I've still got 5.7gb's left to use with | Usenet-News, from a block that I bought year and years ago ! :-) | I've now deleted msnews.microsoft.com from OE | ...now lets' have a rummage..... | regards, Richard ...

how to put in sql code
first of all many thanks to Klatuu who has already helped me a lot!! I create my SQl code by means of some string concatenation. I do this so i can use the values of my field to create my SQl code. The problem is that some of these fields are double-populated eg. field location contains England and Brazil. My string creates "IIf([Table]![Location]" & [Location] & ",'OK', 'problem')" how can i populated the field location so that it is SQl-code? as =(Brazil or England) does not work as =Brazil or = England do...

insert date
Hello, Excel 2000. How might I insert the document last saved date into header or footer? Not into a cell. You need a sub to do this This will only work if you are using Excel 2000 or higher Place this in the thisworkbook module Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In ActiveWindow.SelectedSheets wkSht.RightFooter = "&8Last Saved : " & _ ActiveWorkbook.BuiltinDocumentProperties("Last Save Time") Next wkSht End Sub If you use Excel 97 post back -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rond...

Inserting picture without loss of information
I'm attempting to insert some cropped sections of very high resolution (3000x2000) bitmaps into Excel. The problem is when I insert the cropped images there is some processing done on the image before it is displayed. This processing blurs some of the pixels. I really need the image to retain the pixel information. Is this at all possible? Chris, I've found, though not worked extensively with it, that small pictures will be inserted in actual size. That means if Excel is set to 100% zoom, they'll be rendered pixel-for-pixel on the screen, if the zoom is set to 100%. Large p...