How to Write data to SQL Server?

  • Thread starter Thread starter JG
  • Start date Start date
J

JG

I am converting an Access Database to an SQL Server
database

I have the connection working and am able to delete and
loop through and read records correctly using Set
Rst_Labels
Set RST_LABELS = dbs.OpenRecordset("SELECT * FROM
dbo_labels;", dbOpenDynaset, dbSQLPassThrough)

With RST_LABELS
Loop etc.
..movefirst
..movenext etc

However I now wish to add records to this SQL table(
importing data from a Text file and saving to an SQL Table)

Code is as below which worked for an Access Table. How or
Can I get this to work with an SQL Table or do I have to
rewrite completely..

Set RST_Labels = db.OpenRecordset("dbo.LABELS",
DBOPENTABLE)

This command produces an error message.

With RST_Labels
.AddNew
![HO] = HO
etc.
.Update
End With
TIA
JG
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need quotes around a string (Text) value. E.g.:

..AddNew
!HO = 'HO'
..Update

If HO is the name of a control on a form you'd do this:

..AddNew
!HO = Me!HO
..Update

If Me!HO is NULL & the column [HO] is defined as a NOT NULL column then
you'll get an error. Therefore, you'll have to check if Me!HO is NULL
before attempting to add it's value to the column. E.g.:

..AddNew
If Not IsNull(Me!HO) Then !HO = Me!HO
.... etc. ...
..Update


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQTUG/oechKqOuFEgEQJ7WgCgkjGIHRptqYy4ey6DD5w7fifirRoAn3jq
LEiksUMXiChDUmDgrOkTvzXD
=3JTH
-----END PGP SIGNATURE-----


JG wrote:
 
Thanks for that but my problem is that I cannot open a
Table to write to . The Openrecordset syntax now fails.
But I take from your reply, that the rest of the code
should work in the same way which is good, because I did
not want to start writing SQL INSERT etc unless there is
no alternative.
-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need quotes around a string (Text) value. E.g.:

..AddNew
!HO = 'HO'
..Update

If HO is the name of a control on a form you'd do this:

..AddNew
!HO = Me!HO
..Update

If Me!HO is NULL & the column [HO] is defined as a NOT NULL column then
you'll get an error. Therefore, you'll have to check if Me!HO is NULL
before attempting to add it's value to the column. E.g.:

..AddNew
If Not IsNull(Me!HO) Then !HO = Me!HO
.... etc. ...
..Update


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQTUG/oechKqOuFEgEQJ7WgCgkjGIHRptqYy4ey6DD5w7fifirR oAn3jq
LEiksUMXiChDUmDgrOkTvzXD
=3JTH
-----END PGP SIGNATURE-----


JG wrote:

This command produces an error message.

With RST_Labels
.AddNew
![HO] = HO
etc.
.Update
End With
Click to expand...

.
Click to expand...
 
The answer was in Knowledgebase Article 161252
Set GUS_Labels = ConLabels.OpenRecordset("LABELS",
dbOpenDynamic, 0, dbOptimistic)

Thanks to all for your help
-----Original Message-----
Thanks for that but my problem is that I cannot open a
Table to write to . The Openrecordset syntax now fails.
But I take from your reply, that the rest of the code
should work in the same way which is good, because I did
not want to start writing SQL INSERT etc unless there is
no alternative.
-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need quotes around a string (Text) value. E.g.:

..AddNew
!HO = 'HO'
..Update

If HO is the name of a control on a form you'd do this:

..AddNew
!HO = Me!HO
..Update

If Me!HO is NULL & the column [HO] is defined as a NOT NULL column then
you'll get an error. Therefore, you'll have to check if Me!HO is NULL
before attempting to add it's value to the column. E.g.:

..AddNew
If Not IsNull(Me!HO) Then !HO = Me!HO
.... etc. ...
..Update


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQTUG/oechKqOuFEgEQJ7WgCgkjGIHRptqYy4ey6DD5w7fifir
Click to expand...
R
oAn3jq
LEiksUMXiChDUmDgrOkTvzXD
=3JTH
-----END PGP SIGNATURE-----


JG wrote:

This command produces an error message.

With RST_Labels
.AddNew
![HO] = HO
etc.
.Update
End With
Click to expand...

.
Click to expand...
.
Click to expand...
 
And of course the Help files weren't installed properly
either see KB Article 249065
-----Original Message-----
The answer was in Knowledgebase Article 161252
Set GUS_Labels = ConLabels.OpenRecordset("LABELS",
dbOpenDynamic, 0, dbOptimistic)

Thanks to all for your help
-----Original Message-----
Thanks for that but my problem is that I cannot open a
Table to write to . The Openrecordset syntax now fails.
But I take from your reply, that the rest of the code
should work in the same way which is good, because I did
not want to start writing SQL INSERT etc unless there is
no alternative.
-----Original Message-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You need quotes around a string (Text) value. E.g.:

..AddNew
!HO = 'HO'
..Update

If HO is the name of a control on a form you'd do this:

..AddNew
!HO = Me!HO
..Update

If Me!HO is NULL & the column [HO] is defined as a NOT NULL column then
you'll get an error. Therefore, you'll have to check
Click to expand...
if
Me!HO is NULL
before attempting to add it's value to the column. E.g.:

..AddNew
If Not IsNull(Me!HO) Then !HO = Me!HO
.... etc. ...
..Update


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQTUG/oechKqOuFEgEQJ7WgCgkjGIHRptqYy4ey6DD5w7fifi
Click to expand...
Click to expand...
r
R
oAn3jq
LEiksUMXiChDUmDgrOkTvzXD
=3JTH
-----END PGP SIGNATURE-----


JG wrote:

< SNIP >

This command produces an error message.

With RST_Labels
.AddNew
![HO] = HO
etc.
.Update
End With

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top