Updating an MS-SQL 2K table directly from Excel 2K

This is a multi-part message in MIME format.

------=_NextPart_000_0040_01C3510B.D3F64C90
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Can any one suggest how I can populate a 2 column table in range =
a2:b2000 to a 2 filed table in MS-SQL 2K?

TIA, AlanN
------=_NextPart_000_0040_01C3510B.D3F64C90
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1170" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Can any one suggest how I can populate =
a 2 column=20
table in range a2:b2000 to a 2 filed table in MS-SQL 2K?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>TIA, AlanN</FONT></DIV></BODY></HTML>

------=_NextPart_000_0040_01C3510B.D3F64C90--

0
AlanN
7/23/2003 3:16:13 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
338 Views

Similar Articles

[PageSpeed] 17

This is a multi-part message in MIME format.

------=_NextPart_000_0021_01C3515A.81DB8660
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Here's one way of doing it. No error checking included. Reads data from =
Sheet1, range A1:B10 and appends it to Table1(fields F1, F2) in database =
Test on a local server having first emptied the table. Assumes that the =
data is compatible with F1 and F2 datatypes. Requires a reference to =
Microsoft ActiveX Data Objects 2.x Library.

  Dim cnn As ADODB.Connection
  Dim r As Long
  Dim rst As ADODB.Recordset
  Dim str As String
  str =3D "Provider=3DSQLOLEDB;Server=3D(local);"
  str =3D str & "Database=3DTest;"
  str =3D str & "Trusted_Connection=3Dyes;"
  Set cnn =3D New ADODB.Connection
  cnn.Open str
  cnn.Execute "delete from Table1"
  Set rst =3D New ADODB.Recordset
  rst.Open "Table1", cnn, adOpenKeyset, adLockOptimistic, =
adCmdTableDirect
  With Worksheets("Sheet1")
    For r =3D 1 To 10
      rst.AddNew Array("F1", "F2"), Array(.Cells(r, 1), .Cells(r, 2))
      rst.Update
    Next r
  End With
  rst.Close
  Set rst =3D Nothing

  "AlanN" <SPAMOFF!@hotmail.com> wrote in message =
news:#kUVc1SUDHA.2252@TK2MSFTNGP12.phx.gbl...
  Can any one suggest how I can populate a 2 column table in range =
a2:b2000 to a 2 filed table in MS-SQL 2K?

  TIA, AlanN
------=_NextPart_000_0021_01C3515A.81DB8660
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Here's one way of doing it. =
No&nbsp;error checking=20
included.&nbsp;Reads data from Sheet1, range A1:B10&nbsp;and appends it =
to=20
Table1(fields F1, F2) in database Test on a local server having first =
emptied=20
the table.&nbsp;Assumes that&nbsp;the data is compatible with F1 and F2=20
datatypes. Requires&nbsp;a reference&nbsp;to Microsoft ActiveX Data =
Objects 2.x=20
Library.<BR></FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&nbsp; Dim cnn As =
ADODB.Connection<BR>&nbsp; Dim r=20
As Long<BR>&nbsp; Dim rst As ADODB.Recordset<BR>&nbsp; Dim str As=20
String<BR>&nbsp; str =3D =
"Provider=3DSQLOLEDB;Server=3D(local);"<BR>&nbsp; str =3D str=20
&amp; "Database=3DTest;"<BR>&nbsp; str =3D str &amp;=20
"Trusted_Connection=3Dyes;"<BR>&nbsp; Set cnn =3D New =
ADODB.Connection<BR>&nbsp;=20
cnn.Open str<BR>&nbsp; cnn.Execute "delete from Table1"<BR>&nbsp; Set =
rst =3D New=20
ADODB.Recordset<BR>&nbsp; rst.Open "Table1", cnn, adOpenKeyset,=20
adLockOptimistic, adCmdTableDirect<BR>&nbsp; With=20
Worksheets("Sheet1")<BR>&nbsp;&nbsp;&nbsp; For r =3D 1 To=20
10<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rst.AddNew Array("F1", "F2"),=20
Array(.Cells(r, 1), .Cells(r, 2))<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
rst.Update<BR>&nbsp;&nbsp;&nbsp; Next r<BR>&nbsp; End With<BR>&nbsp;=20
rst.Close<BR>&nbsp; Set rst =3D Nothing<BR></DIV></FONT>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV>"AlanN" &lt;<A=20
  href=3D"mailto:SPAMOFF!@hotmail.com">SPAMOFF!@hotmail.com</A>&gt; =
wrote in=20
  message <A=20
  =
href=3D"news:#kUVc1SUDHA.2252@TK2MSFTNGP12.phx.gbl">news:#kUVc1SUDHA.2252=
@TK2MSFTNGP12.phx.gbl</A>...</DIV>
  <DIV><FONT face=3DArial size=3D2>Can any one suggest how I can =
populate a 2 column=20
  table in range a2:b2000 to a 2 filed table in MS-SQL 2K?</FONT></DIV>
  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>TIA, =
AlanN</FONT></DIV></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_0021_01C3515A.81DB8660--

0
rob4110 (3)
7/23/2003 7:39:26 PM
This is a multi-part message in MIME format.

------=_NextPart_000_0009_01C351CE.3F2D92D0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Many thanks for taking the time...

I get an error from the first line- "Compile error: User defined type =
not defined."
I assume it relates to a reference to Microsoft ActiveX Data Objects 2.x =
Library not being available.
How does one go about installing this?

TIA, AlanN
  "Rob Rutherford" <rob@rcrutherford.invalid> wrote in message =
news:bfmob4$481$1@newsg1.svr.pol.co.uk...
  Here's one way of doing it. No error checking included. Reads data =
from Sheet1, range A1:B10 and appends it to Table1(fields F1, F2) in =
database Test on a local server having first emptied the table. Assumes =
that the data is compatible with F1 and F2 datatypes. Requires a =
reference to Microsoft ActiveX Data Objects 2.x Library.

    Dim cnn As ADODB.Connection
    Dim r As Long
    Dim rst As ADODB.Recordset
    Dim str As String
    str =3D "Provider=3DSQLOLEDB;Server=3D(local);"
    str =3D str & "Database=3DTest;"
    str =3D str & "Trusted_Connection=3Dyes;"
    Set cnn =3D New ADODB.Connection
    cnn.Open str
    cnn.Execute "delete from Table1"
    Set rst =3D New ADODB.Recordset
    rst.Open "Table1", cnn, adOpenKeyset, adLockOptimistic, =
adCmdTableDirect
    With Worksheets("Sheet1")
      For r =3D 1 To 10
        rst.AddNew Array("F1", "F2"), Array(.Cells(r, 1), .Cells(r, 2))
        rst.Update
      Next r
    End With
    rst.Close
    Set rst =3D Nothing

    "AlanN" <SPAMOFF!@hotmail.com> wrote in message =
news:#kUVc1SUDHA.2252@TK2MSFTNGP12.phx.gbl...
    Can any one suggest how I can populate a 2 column table in range =
a2:b2000 to a 2 filed table in MS-SQL 2K?

    TIA, AlanN
------=_NextPart_000_0009_01C351CE.3F2D92D0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1170" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Many thanks for taking the =
time...</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I get an error&nbsp;from the first =
line- "Compile=20
error: User defined type not defined."</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>I assume it relates to a =
reference&nbsp;to=20
Microsoft ActiveX Data Objects 2.x Library not being =
available.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>How does one go about installing =
this?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>TIA, AlanN</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV>"Rob Rutherford" &lt;<A=20
  =
href=3D"mailto:rob@rcrutherford.invalid">rob@rcrutherford.invalid</A>&gt;=
 wrote=20
  in message <A=20
  =
href=3D"news:bfmob4$481$1@newsg1.svr.pol.co.uk">news:bfmob4$481$1@newsg1.=
svr.pol.co.uk</A>...</DIV>
  <DIV><FONT face=3DArial size=3D2>Here's one way of doing it. =
No&nbsp;error=20
  checking included.&nbsp;Reads data from Sheet1, range A1:B10&nbsp;and =
appends=20
  it to Table1(fields F1, F2) in database Test on a local server having =
first=20
  emptied the table.&nbsp;Assumes that&nbsp;the data is compatible with =
F1 and=20
  F2 datatypes. Requires&nbsp;a reference&nbsp;to Microsoft ActiveX Data =
Objects=20
  2.x Library.<BR></FONT></DIV>
  <DIV><FONT face=3DArial size=3D2>&nbsp; Dim cnn As =
ADODB.Connection<BR>&nbsp; Dim=20
  r As Long<BR>&nbsp; Dim rst As ADODB.Recordset<BR>&nbsp; Dim str As=20
  String<BR>&nbsp; str =3D =
"Provider=3DSQLOLEDB;Server=3D(local);"<BR>&nbsp; str =3D str=20
  &amp; "Database=3DTest;"<BR>&nbsp; str =3D str &amp;=20
  "Trusted_Connection=3Dyes;"<BR>&nbsp; Set cnn =3D New =
ADODB.Connection<BR>&nbsp;=20
  cnn.Open str<BR>&nbsp; cnn.Execute "delete from Table1"<BR>&nbsp; Set =
rst =3D=20
  New ADODB.Recordset<BR>&nbsp; rst.Open "Table1", cnn, adOpenKeyset,=20
  adLockOptimistic, adCmdTableDirect<BR>&nbsp; With=20
  Worksheets("Sheet1")<BR>&nbsp;&nbsp;&nbsp; For r =3D 1 To=20
  10<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rst.AddNew Array("F1", "F2"),=20
  Array(.Cells(r, 1), .Cells(r, 2))<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
  rst.Update<BR>&nbsp;&nbsp;&nbsp; Next r<BR>&nbsp; End With<BR>&nbsp;=20
  rst.Close<BR>&nbsp; Set rst =3D Nothing<BR></DIV></FONT>
  <BLOCKQUOTE dir=3Dltr=20
  style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
    <DIV>"AlanN" &lt;<A=20
    href=3D"mailto:SPAMOFF!@hotmail.com">SPAMOFF!@hotmail.com</A>&gt; =
wrote in=20
    message <A=20
    =
href=3D"news:#kUVc1SUDHA.2252@TK2MSFTNGP12.phx.gbl">news:#kUVc1SUDHA.2252=
@TK2MSFTNGP12.phx.gbl</A>...</DIV>
    <DIV><FONT face=3DArial size=3D2>Can any one suggest how I can =
populate a 2=20
    column table in range a2:b2000 to a 2 filed table in MS-SQL =
2K?</FONT></DIV>
    <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
    <DIV><FONT face=3DArial size=3D2>TIA,=20
AlanN</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_0009_01C351CE.3F2D92D0--

0
AlanN
7/24/2003 2:27:56 PM
This is a multi-part message in MIME format.

------=_NextPart_000_0025_01C351E5.ABE02520
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Nevermind, figured it out.
Thanks AlanN
  "AlanN" <SPAMOFF!@hotmail.com> wrote in message =
news:eDpnH$eUDHA.3308@tk2msftngp13.phx.gbl...
  Many thanks for taking the time...

  I get an error from the first line- "Compile error: User defined type =
not defined."
  I assume it relates to a reference to Microsoft ActiveX Data Objects =
2.x Library not being available.
  How does one go about installing this?

  TIA, AlanN
    "Rob Rutherford" <rob@rcrutherford.invalid> wrote in message =
news:bfmob4$481$1@newsg1.svr.pol.co.uk...
    Here's one way of doing it. No error checking included. Reads data =
from Sheet1, range A1:B10 and appends it to Table1(fields F1, F2) in =
database Test on a local server having first emptied the table. Assumes =
that the data is compatible with F1 and F2 datatypes. Requires a =
reference to Microsoft ActiveX Data Objects 2.x Library.

      Dim cnn As ADODB.Connection
      Dim r As Long
      Dim rst As ADODB.Recordset
      Dim str As String
      str =3D "Provider=3DSQLOLEDB;Server=3D(local);"
      str =3D str & "Database=3DTest;"
      str =3D str & "Trusted_Connection=3Dyes;"
      Set cnn =3D New ADODB.Connection
      cnn.Open str
      cnn.Execute "delete from Table1"
      Set rst =3D New ADODB.Recordset
      rst.Open "Table1", cnn, adOpenKeyset, adLockOptimistic, =
adCmdTableDirect
      With Worksheets("Sheet1")
        For r =3D 1 To 10
          rst.AddNew Array("F1", "F2"), Array(.Cells(r, 1), .Cells(r, =
2))
          rst.Update
        Next r
      End With
      rst.Close
      Set rst =3D Nothing

      "AlanN" <SPAMOFF!@hotmail.com> wrote in message =
news:#kUVc1SUDHA.2252@TK2MSFTNGP12.phx.gbl...
      Can any one suggest how I can populate a 2 column table in range =
a2:b2000 to a 2 filed table in MS-SQL 2K?

      TIA, AlanN
------=_NextPart_000_0025_01C351E5.ABE02520
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1170" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Nevermind, figured it out.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Thanks AlanN</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV>"AlanN" &lt;<A=20
  href=3D"mailto:SPAMOFF!@hotmail.com">SPAMOFF!@hotmail.com</A>&gt; =
wrote in=20
  message <A=20
  =
href=3D"news:eDpnH$eUDHA.3308@tk2msftngp13.phx.gbl">news:eDpnH$eUDHA.3308=
@tk2msftngp13.phx.gbl</A>...</DIV>
  <DIV><FONT face=3DArial size=3D2>Many thanks for taking the =
time...</FONT></DIV>
  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>I get an error&nbsp;from the first =
line- "Compile=20
  error: User defined type not defined."</FONT></DIV>
  <DIV><FONT face=3DArial size=3D2>I assume it relates to a =
reference&nbsp;to=20
  Microsoft ActiveX Data Objects 2.x Library not being =
available.</FONT></DIV>
  <DIV><FONT face=3DArial size=3D2>How does one go about installing=20
  this?</FONT></DIV>
  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>TIA, AlanN</FONT></DIV>
  <BLOCKQUOTE dir=3Dltr=20
  style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
    <DIV>"Rob Rutherford" &lt;<A=20
    =
href=3D"mailto:rob@rcrutherford.invalid">rob@rcrutherford.invalid</A>&gt;=
=20
    wrote in message <A=20
    =
href=3D"news:bfmob4$481$1@newsg1.svr.pol.co.uk">news:bfmob4$481$1@newsg1.=
svr.pol.co.uk</A>...</DIV>
    <DIV><FONT face=3DArial size=3D2>Here's one way of doing it. =
No&nbsp;error=20
    checking included.&nbsp;Reads data from Sheet1, range =
A1:B10&nbsp;and=20
    appends it to Table1(fields F1, F2) in database Test on a local =
server=20
    having first emptied the table.&nbsp;Assumes that&nbsp;the data is=20
    compatible with F1 and F2 datatypes. Requires&nbsp;a =
reference&nbsp;to=20
    Microsoft ActiveX Data Objects 2.x Library.<BR></FONT></DIV>
    <DIV><FONT face=3DArial size=3D2>&nbsp; Dim cnn As =
ADODB.Connection<BR>&nbsp;=20
    Dim r As Long<BR>&nbsp; Dim rst As ADODB.Recordset<BR>&nbsp; Dim str =
As=20
    String<BR>&nbsp; str =3D =
"Provider=3DSQLOLEDB;Server=3D(local);"<BR>&nbsp; str =3D=20
    str &amp; "Database=3DTest;"<BR>&nbsp; str =3D str &amp;=20
    "Trusted_Connection=3Dyes;"<BR>&nbsp; Set cnn =3D New =
ADODB.Connection<BR>&nbsp;=20
    cnn.Open str<BR>&nbsp; cnn.Execute "delete from Table1"<BR>&nbsp; =
Set rst =3D=20
    New ADODB.Recordset<BR>&nbsp; rst.Open "Table1", cnn, adOpenKeyset,=20
    adLockOptimistic, adCmdTableDirect<BR>&nbsp; With=20
    Worksheets("Sheet1")<BR>&nbsp;&nbsp;&nbsp; For r =3D 1 To=20
    10<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rst.AddNew Array("F1", "F2"),=20
    Array(.Cells(r, 1), .Cells(r, 2))<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
    rst.Update<BR>&nbsp;&nbsp;&nbsp; Next r<BR>&nbsp; End With<BR>&nbsp; =

    rst.Close<BR>&nbsp; Set rst =3D Nothing<BR></DIV></FONT>
    <BLOCKQUOTE dir=3Dltr=20
    style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
      <DIV>"AlanN" &lt;<A=20
      href=3D"mailto:SPAMOFF!@hotmail.com">SPAMOFF!@hotmail.com</A>&gt; =
wrote in=20
      message <A=20
      =
href=3D"news:#kUVc1SUDHA.2252@TK2MSFTNGP12.phx.gbl">news:#kUVc1SUDHA.2252=
@TK2MSFTNGP12.phx.gbl</A>...</DIV>
      <DIV><FONT face=3DArial size=3D2>Can any one suggest how I can =
populate a 2=20
      column table in range a2:b2000 to a 2 filed table in MS-SQL=20
      2K?</FONT></DIV>
      <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
      <DIV><FONT face=3DArial size=3D2>TIA,=20
AlanN</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_0025_01C351E5.ABE02520--

0
AlanN
7/24/2003 5:15:36 PM
This is a multi-part message in MIME format.

------=_NextPart_000_001C_01C35219.86D6F400
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

In the VB editor select Tools > References. If you have ADO installed =
you will see one or more Microsoft ActiveX Data Objects Libraries listed =
in the Available References box. If so, tick the latest version. If not, =
you can get ADO by downloading the Microsoft Data Access Components =
(MDAC) from their site (www.microsoft.com) or, if you don't want to do =
this, you could try another approach. For example, use DAO instead of =
ADO (I've not done this myself), or, if you have Access you could link =
your Excel table and your SQL Server table to an Access database and =
copy the data with an Access query.
  "AlanN" <SPAMOFF!@hotmail.com> wrote in message =
news:eDpnH$eUDHA.3308@tk2msftngp13.phx.gbl...
  Many thanks for taking the time...

  I get an error from the first line- "Compile error: User defined type =
not defined."
  I assume it relates to a reference to Microsoft ActiveX Data Objects =
2.x Library not being available.
  How does one go about installing this?

  TIA, AlanN
    "Rob Rutherford" <rob@rcrutherford.invalid> wrote in message =
news:bfmob4$481$1@newsg1.svr.pol.co.uk...
    Here's one way of doing it. No error checking included. Reads data =
from Sheet1, range A1:B10 and appends it to Table1(fields F1, F2) in =
database Test on a local server having first emptied the table. Assumes =
that the data is compatible with F1 and F2 datatypes. Requires a =
reference to Microsoft ActiveX Data Objects 2.x Library.

      Dim cnn As ADODB.Connection
      Dim r As Long
      Dim rst As ADODB.Recordset
      Dim str As String
      str =3D "Provider=3DSQLOLEDB;Server=3D(local);"
      str =3D str & "Database=3DTest;"
      str =3D str & "Trusted_Connection=3Dyes;"
      Set cnn =3D New ADODB.Connection
      cnn.Open str
      cnn.Execute "delete from Table1"
      Set rst =3D New ADODB.Recordset
      rst.Open "Table1", cnn, adOpenKeyset, adLockOptimistic, =
adCmdTableDirect
      With Worksheets("Sheet1")
        For r =3D 1 To 10
          rst.AddNew Array("F1", "F2"), Array(.Cells(r, 1), .Cells(r, =
2))
          rst.Update
        Next r
      End With
      rst.Close
      Set rst =3D Nothing

      "AlanN" <SPAMOFF!@hotmail.com> wrote in message =
news:#kUVc1SUDHA.2252@TK2MSFTNGP12.phx.gbl...
      Can any one suggest how I can populate a 2 column table in range =
a2:b2000 to a 2 filed table in MS-SQL 2K?

      TIA, AlanN
------=_NextPart_000_001C_01C35219.86D6F400
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>In the VB editor select Tools &gt; =
References. If=20
you have ADO installed you will see one or more Microsoft ActiveX Data =
Objects=20
Libraries listed in the Available References box. If so, tick the latest =

version. If not, you can get ADO by downloading the Microsoft Data =
Access=20
Components (MDAC) from their site (<A=20
href=3D"http://www.microsoft.com">www.microsoft.com</A>) or, if you =
don't want to=20
do this, you could try another approach. For example, use DAO instead of =
ADO=20
(I've not done this myself), or, if you have Access you could link your =
Excel=20
table and your SQL Server table to an Access database and copy the data =
with an=20
Access&nbsp;query.</FONT></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV>"AlanN" &lt;<A=20
  href=3D"mailto:SPAMOFF!@hotmail.com">SPAMOFF!@hotmail.com</A>&gt; =
wrote in=20
  message <A=20
  =
href=3D"news:eDpnH$eUDHA.3308@tk2msftngp13.phx.gbl">news:eDpnH$eUDHA.3308=
@tk2msftngp13.phx.gbl</A>...</DIV>
  <DIV><FONT face=3DArial size=3D2>Many thanks for taking the =
time...</FONT></DIV>
  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>I get an error&nbsp;from the first =
line- "Compile=20
  error: User defined type not defined."</FONT></DIV>
  <DIV><FONT face=3DArial size=3D2>I assume it relates to a =
reference&nbsp;to=20
  Microsoft ActiveX Data Objects 2.x Library not being =
available.</FONT></DIV>
  <DIV><FONT face=3DArial size=3D2>How does one go about installing=20
  this?</FONT></DIV>
  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>TIA, AlanN</FONT></DIV>
  <BLOCKQUOTE dir=3Dltr=20
  style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
    <DIV>"Rob Rutherford" &lt;<A=20
    =
href=3D"mailto:rob@rcrutherford.invalid">rob@rcrutherford.invalid</A>&gt;=
=20
    wrote in message <A=20
    =
href=3D"news:bfmob4$481$1@newsg1.svr.pol.co.uk">news:bfmob4$481$1@newsg1.=
svr.pol.co.uk</A>...</DIV>
    <DIV><FONT face=3DArial size=3D2>Here's one way of doing it. =
No&nbsp;error=20
    checking included.&nbsp;Reads data from Sheet1, range =
A1:B10&nbsp;and=20
    appends it to Table1(fields F1, F2) in database Test on a local =
server=20
    having first emptied the table.&nbsp;Assumes that&nbsp;the data is=20
    compatible with F1 and F2 datatypes. Requires&nbsp;a =
reference&nbsp;to=20
    Microsoft ActiveX Data Objects 2.x Library.<BR></FONT></DIV>
    <DIV><FONT face=3DArial size=3D2>&nbsp; Dim cnn As =
ADODB.Connection<BR>&nbsp;=20
    Dim r As Long<BR>&nbsp; Dim rst As ADODB.Recordset<BR>&nbsp; Dim str =
As=20
    String<BR>&nbsp; str =3D =
"Provider=3DSQLOLEDB;Server=3D(local);"<BR>&nbsp; str =3D=20
    str &amp; "Database=3DTest;"<BR>&nbsp; str =3D str &amp;=20
    "Trusted_Connection=3Dyes;"<BR>&nbsp; Set cnn =3D New =
ADODB.Connection<BR>&nbsp;=20
    cnn.Open str<BR>&nbsp; cnn.Execute "delete from Table1"<BR>&nbsp; =
Set rst =3D=20
    New ADODB.Recordset<BR>&nbsp; rst.Open "Table1", cnn, adOpenKeyset,=20
    adLockOptimistic, adCmdTableDirect<BR>&nbsp; With=20
    Worksheets("Sheet1")<BR>&nbsp;&nbsp;&nbsp; For r =3D 1 To=20
    10<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rst.AddNew Array("F1", "F2"),=20
    Array(.Cells(r, 1), .Cells(r, 2))<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
    rst.Update<BR>&nbsp;&nbsp;&nbsp; Next r<BR>&nbsp; End With<BR>&nbsp; =

    rst.Close<BR>&nbsp; Set rst =3D Nothing<BR></DIV></FONT>
    <BLOCKQUOTE dir=3Dltr=20
    style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
      <DIV>"AlanN" &lt;<A=20
      href=3D"mailto:SPAMOFF!@hotmail.com">SPAMOFF!@hotmail.com</A>&gt; =
wrote in=20
      message <A=20
      =
href=3D"news:#kUVc1SUDHA.2252@TK2MSFTNGP12.phx.gbl">news:#kUVc1SUDHA.2252=
@TK2MSFTNGP12.phx.gbl</A>...</DIV>
      <DIV><FONT face=3DArial size=3D2>Can any one suggest how I can =
populate a 2=20
      column table in range a2:b2000 to a 2 filed table in MS-SQL=20
      2K?</FONT></DIV>
      <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
      <DIV><FONT face=3DArial size=3D2>TIA,=20
AlanN</FONT></DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_001C_01C35219.86D6F400--

0
rob4110 (3)
7/24/2003 6:26:48 PM
Reply:

Similar Artilces:

Excel 07 selects 3 cells when I click on 1
Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an empty cell 2 or more below it are selected and the typing goes on the bottom row. I just want to select the cell I click on. Thanks Are the cells merged?? I don't like the 2007 either! If i could i would have the look and feel of 2003 on the engine of 2007... :-( On Thu, 1 Nov 2007 20:21:01 -0700, Brian <Brian@discussions.microsoft.com> wrote: >Hi I just got 2007 (I want 2000 back) and a lot of the time when click on an >empty cell 2 or more below it are selected and the typing goes on the botto...

Excel 2003/edit/replace. Under Options/Look In, values is AWOL bu.
Under Look In options Find allows for search by formulae OR values OR comments Replace is restricted to formulaes ONLY WHY? cp Have a look at this google search result. This topic came up a couple days ago and was addressed by Dave and Myrna. http://snipurl.com/cd9j Gord Dibben Excel MVP On Fri, 28 Jan 2005 03:49:02 -0800, "cp" <cp@discussions.microsoft.com> wrote: >Under Look In options >Find allows for search by formulae OR values OR comments >Replace is restricted to formulaes ONLY >WHY? ...

MS CRM 3.0 and Exchange Server 2000
I have a query regarding installation of CRM E-mail Router. I have a MS CRM 3.0 installed on a Windows 2003 Server, we're using a Exchange Server 2000 on W2K Server SP4, can I install the Router on the Exchange Server 2000? check the implementaiton guide, it gives you system requirements "John Ding" wrote: > I have a query regarding installation of CRM E-mail Router. I have a MS CRM > 3.0 installed on a Windows 2003 Server, we're using a Exchange Server 2000 on > W2K Server SP4, can I install the Router on the Exchange Server 2000? > > > I have...

how do i enable dde in excel?
i am posting a DDE link into an excel cell from an external program and getting the message ' DDE Not enabled' in the cell once the link is pasted in. how do i enable DDE? ...

Jon Peltier
Jon, How would you do this with a Pivot Table? I'm not a Pivot Table novice and I just can't envision how this one would be done. Thanks, Barb Hi Barb - I didn't see this question at first, but I responded in the original thread. I didn't try to replicate the OP's one column setup from his first post, because it didn't match the more logical arrangement in his post in the other forum. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Barb Reinhardt wrote: > Jon, > ...

excel file can't be opened #2
hi,I'm cho I have a recent excel file (about 1 month),but since two days ago,the file can't be opened completely or always not responding. What should I do so that I can use this important file. Any help would be appreciated regards, cho ...

How to transpose in excel file
Hi I would like to make a transpose like this? From House Defect How much #1 Broken Sinks 1000 #1 Bad Paint 2000 #1 Bad Lighting 1000 #2 Broken Sinks 0 #2 Bad Paint 2000 #2 Bad Lighting 100 and Transpose into House Broken Sinks Bad Paint Bad Lighting #1 1000 2000 1000 #2 0 2000 100 A Pivot Table will do that and more. Set House as row field, Defect as column field and How much as data. HTH. Best wishes Harald "vilfood" <vilfood@d...

Install this internet package from MS Corporation
--rwucuosxjmw Content-Type: multipart/related; boundary="wvfaazvnt"; type="multipart/alternative" --wvfaazvnt Content-Type: multipart/alternative; boundary="dpzsgxlysayp" --dpzsgxlysayp Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Consumer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to help protect your computer from these vulnerabilities, the most...

Word table to XML
Can some one help me in converting a word table to XML? I am using word 2003 and the macro should word even with word 2007. Thanks in advance. Which macro? What is the xml schema for the table? Yves "Sri Vidya" <Sri Vidya@discussions.microsoft.com> wrote in message news:F93D7E1C-7BC9-456A-90CC-920CF0219759@microsoft.com... > Can some one help me in converting a word table to XML? I am using word > 2003 > and the macro should word even with word 2007. > Thanks in advance. > > ...

Headers in Excel #3
Can you tell me if it's possible and if so, how I can change the header cells, i.e. A B C D E ... etc? Thanks. Andrea (508) 842-3880 Hi Andrea no this is not possible. The best workaround you can achieve is: - use row 1 as header - goto cell A2 and to the menu 'Windows - Freeze Panes' to always show row 1 - in 'Tools - Options - view' you can hide the row and column headers -- Regards Frank Kabel Frankfurt, Germany "Andrea" <shrews.receptionist@nitco-lift.com> schrieb im Newsbeitrag news:12ff301c44354$b88982b0$a501280a@phx.gbl... > Can you tell ...

Pivot Table Axis format
Hi. I have a macro that creates a pivot-table and pivot chart. The X-axis is a date. In the raw data, the date is formated as month/day. This is the way I want it on the chart. However, the X-axis formats with month/day/year. How can I force this to display correctly? Thanks, Mike. Do you see a grey little dropdown under the X axis (where you can choose your dates to show)? If yes, then rightclick on it. Select the top option: "Format pivotchart field" then click on the Number button on the next dialog. There are a bunch of date formats that you can choose from. Maybe...

Excel -> Access Import Bizarreness
A guy gave me an Excel file that he needs me to covert to XML. No problem. Slurp it into a database and use a DataSet.WriteXML() call to turn it into an XML file. But I can't import it into Access because it has "Merged Cells". For example, data that should look like this: ColA ColB ----- ----- Rec1 this Rec2 this Rec3 this Rec4 that Rec5 Other The three "this" cells have been merged into one tall cell: ColA ColB ----- ----- Rec1 this Rec2 Rec3 Rec4 that Rec5 Other I need to "unmerge" these, but I know almost nothi...

help for sql select command !!!
Hi Everyone! Imagine we have 3 checkBoxes in a search page and 3 boolean fields in DB table and every checkbox is for one of these fields . In search page users check every 3 checkBoxes and what i want is that first it shows the records that every their 3 fields are checked then 2 and then 1(DESC). BTW my DB is MS Access. Please help me ! On Mar 1, 7:17=A0pm, "miladha...@gmail.com" <miladha...@gmail.com> wrote: > Hi Everyone! > Imagine we have 3 checkBoxes in a search page and 3 boolean fields in > DB table and every checkbox is for one of these fields . I...

Is this possible within Excel 2000?
Hi, I've used Excel on a get by basis for many years at work, and I'm just beginning to realise its vast capabilities, having viewed several groups which I didn't know existed previously. I subsequently would like to ask if it is possible for Excel to look at the numerical or text content of a cell on one worksheet and then look at a predefined range on another worksheet and find the equivalent cell content and then move the cursor to that cell? This has me stumped presently. Thanks in advance for any assistance. J.J. To actually move the selection, you'd need to use a litt...

Excel 2000 fail to open workbook
Hi, I have one Excel template. If we open it on Excel 2000, the Excel will hang, and the process occupy cpu 95% ~98%. But we can open this tempalte in Excel 2003 normally, and if we save this tempalte in Excel 2003, and then open it in Excel 2000, it work normally too. Excel 2000 (9.0.3821 SR-1) Excel 2003 (11.8231.8221) sp3 Would you help us to find the reason for this issue? Thanks, Nancy Hi Nancy, Perhaps there was some (probably minor) corruption/inconsistency in the workbook that Excel 2003 could fix but Excel 2000 couldn't. -- Cheers macropod [Microsoft MVP - Word] ...

How can I create an invoicing database in Excel?
I would like to know how to create an invoicing database, or register in Excel. (Invoice + tax = total) Are there any templates free online? thanks How about a web search? http://office.microsoft.com/en-us/templates/default.aspx search for invoice -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Vcollins" <Vcollins@discussions.microsoft.com> wrote in message news:C46E9FE2-0B38-4468-9B00-126B7EE5C9A2@microsoft.com... >I would like to know how to create an invoicing database, or register in &...

Excel prints some sheets very small
When I try to print multiple sheets of the same workbook at the same time, one of them (a different one each time) prints very small (about an inch tall) in the upper left hand corner. I am printing from a network file but I don't know if this has anything to do with it or not. Please HELP!!! ...

EXCEL 2007 acting up
j$ = "THIS MACRO WILL ERASE THE SECOND & THIRD SET OF DATA" + Chr$(13) + Chr$(13) Using EXCEL 2007, I get an error with the line above as a macro. It does not recognize CHR$. What am I doing wrong? Thanks Not for me it didn't. Check for MISSING references in Tools>References, uncheck them if there are any. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "pcor" <pcor@discussions.microsoft.com> wrote in message news:F63F6E95-AF0A-4927-B337-99B5C48B92B1@microsoft.com... > j$ = "THIS MACRO WILL...

problems loading excel documents
When I try to open up an excel document via my documents it says that the same document is open already how can this be stopped Tina First try the standard fix(es)......Tools>Options>General uncheck "ignore other applications" OR Start>Run "excel.exe /regserver"(no quotes and note the space before the / mark). You may have to enter your full path to excel.exe....in that case surround with quotes as in..... "C:\mypath\to Excel\somewhere\excel.exe" /regserver Gord Dibben XL2002 On Sat, 15 Nov 2003 09:16:14 -0800, "Tina" <TinaSCovell...

how to convert word file into excel file
If any body know about how to convert MS Word file into MS Excel file please let me know. I am unable to do so. Help me Rizwan Wrote: > If any body know about how to convert MS Word file into MS Excel file > please > let me know. > I am unable to do so. > > Help me Hi Rizwan Try Copy and Paste -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24783 View this thread: http://www.excelforum.com/showthread.php?threadid=467752 Dear Pau...

auto file path update when excel sheet moved to another directory.
Hello, I've been working on a huge excel file for a while and I'm trying to make it easier to use. My problem is that when it is moved from the template folder to a working folder the formulas referencing a file in the template folder have their path changed in the formula and have to have the links updated. Since the file is protected it's impossible for the users to chance the source. It's wierd because it will still pull information, but if information is changed in the external data file the updated doesn't get ported over. I need some like the $ command to ...

Pivot table source about to exceed 64K rows
I have a pivot table that I use for reconciliation between two data types in a database - business unit and business category, both set up to track country but with slightly different uses. Through the first six months of the fiscal year I'm up to about 50,000 rows. I'd like to keep the reconciliation set up as is and continue to refresh the pivot tables with new data each month. Probably next month this data will exceed 64,000 rows. Is there a way to get a pivot table to use two tabs of data as its source? Conceivably I could cut the new months to a new tab. Or if someone has an...

Installing SQL 2000 and SQL 2008 on the same server ?
Hello, I have a small question, is it possible to install a SQL server 2000 and a sql 2008 server on the same server with Windows 2008 server ? For sql2005 ans 2008 it's ok (http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/6cd82ab9-cc97-46b4-bd65-ce5ac23bd6e2/) but for sql 2000? Thanks, Didier Yes. "Didier" <Didier@discussions.microsoft.com> wrote in message news:4E0A0A4D-9FD6-4169-ABC9-506AD9B4BB1D@microsoft.com... > Hello, > > I have a small question, is it possible to install a SQL server 2000 and a > sql 20...

No print settings in Excel HTML files
Is there a way to prevent users from printing the HTML files that are created from an Excel workbook ? I see there is html code to do this, but I don't know how to put that into the Excel workbook so that it is always there when the files are updated (which they are frequently). ...

excel graphs
I am trying to make an excel graph that has a hatch mark in the y axis. I want my graph to be in intevervals of 5 up to 35 and then jump to 70. Is this possible? See Jon Peltier's instructions on Broken Y Axes here. http://peltiertech.com/Excel/Charts/BrokenYAxis.html "CHOP04" <CHOP04@discussions.microsoft.com> wrote in message news:11CD1ED4-84D8-4AE7-BD91-E2EAB1A996CA@microsoft.com... > I am trying to make an excel graph that has a hatch mark in the y axis. I > want my graph to be in intevervals of 5 up to 35 and then jump to 70. Is > this possible? ...