SQL HELP: Access to SQL Server 
Author Message
 SQL HELP: Access to SQL Server

I am writing an application that queries an Access database on the local
client and then performs an INSERT to a SQL database over TCP/IP. I have all
the pieces of code written to do this, but I'm having trouble with some of
the details...

Here is the INSERT statement that I am generating with code....

INSERT INTO RE_Listing
                      (Address, Address2, Bathrooms, BedRooms, City,
CreatedByID, DateCreated, DateModified, ModifiedByID, Price, RE_ListingID,
RE_ListingName,
                      SqFT, State, ZIP, Picture, FK_RE_ListingTypeID, MLS,
Description, isActive, Uploaded)
VALUES     ('101 Elm St.', '', 2, 4, 'Bradenton',
'B8C4F0A1-B3C7-4C33-BC06AC4C92B51779', 4 / 11 / 2002 2 : 05 : 06 PM, 4 / 24
/ 2002 1 : 12 : 27 PM,
                      '2D391723-DB92-497D-902AFE6009739218', 210000,
'BD517CB2-4ABF-4C54-BEE5A3936239529E', '101 Elm St.', 1890, 'FL', '34232',
'',
                      '6679E3C0-FEF1-4E58-947FA49FE7994036', '56789', 'Nice
lovely home is a quiet location.', 1, 0)
WHERE     Uploaded = 0

When I try to run this in VB, I get an error occured near '6'. When I run
this SQL in SQL Server, I get error occurred near '2'. My quotes are in the
right place and I'm assuming the problem is my dates. They are date data
types coming out of my access database going into a SQL date field. Do I
need to do any special formatting to get the date to be accepted? I can send
code if needed... Help!

Thanks,
Steve



Sat, 16 Oct 2004 22:46:42 GMT  
 SQL HELP: Access to SQL Server
Hi,

In case of Access database you need to wrap your date
values into #4/11/2002 2 : 05 : 06 PM#. In case of SQL
Server into single quotes '4/11/ 2002 2 : 05 : 06 PM'.
Otherwise it will be treated as an arithmetic expression
4/11/2002

Val

Quote:
>-----Original Message-----
>I am writing an application that queries an Access

database on the local
Quote:
>client and then performs an INSERT to a SQL database over
TCP/IP. I have all
>the pieces of code written to do this, but I'm having

trouble with some of
Quote:
>the details...

>Here is the INSERT statement that I am generating with
code....

>INSERT INTO RE_Listing
>                      (Address, Address2, Bathrooms,
BedRooms, City,
>CreatedByID, DateCreated, DateModified, ModifiedByID,

Price, RE_ListingID,
Quote:
>RE_ListingName,
>                      SqFT, State, ZIP, Picture,

FK_RE_ListingTypeID, MLS,
Quote:
>Description, isActive, Uploaded)
>VALUES     ('101 Elm St.', '', 2, 4, 'Bradenton',
>'B8C4F0A1-B3C7-4C33-BC06AC4C92B51779', 4 / 11 / 2002 2 :
05 : 06 PM, 4 / 24
>/ 2002 1 : 12 : 27 PM,
>                      '2D391723-DB92-497D-

902AFE6009739218', 210000,
Quote:
>'BD517CB2-4ABF-4C54-BEE5A3936239529E', '101 Elm St.',

1890, 'FL', '34232',
Quote:
>'',
>                      '6679E3C0-FEF1-4E58-

947FA49FE7994036', '56789', 'Nice
Quote:
>lovely home is a quiet location.', 1, 0)
>WHERE     Uploaded = 0

>When I try to run this in VB, I get an error occured

near '6'. When I run
Quote:
>this SQL in SQL Server, I get error occurred near '2'. My
quotes are in the
>right place and I'm assuming the problem is my dates.
They are date data
>types coming out of my access database going into a SQL
date field. Do I
>need to do any special formatting to get the date to be

accepted? I can send
Quote:
>code if needed... Help!

>Thanks,
>Steve

>.



Sat, 16 Oct 2004 23:35:19 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. sql server connect. Migration Access->sql server

2. sql server connect. Migration Access->sql server

3. sql server connect. Migration Access->sql server

4. Access 2000 VBA/SQL - cannot create pseudo-index on linked SQL Server table

5. Executing sql scripts on a SQL server from Access

6. Sql - Dates query to SQL Server and access

7. SQL-syntax SQL-Server 7.0 / Access 97

8. Application Development: MS-Access, Visual Basic, SQL Server, SQL Anywhere

9. Update SQL Syntax to Access and SQL Server

10. SQL syntax across SQL Server..Qracle..Access

11. SQL updateable in Access but not SQL Server

12. HELP!!..accessing access 2000 with linked SQL Server tables

 

 
Powered by phpBB® Forum Software